This is a fun thought exercise…. in MS SQL Server, use a linked server to compare how many rows are in each table of a backup development server with how many rows are in the actual production server. This would be especially relevant if you just did a database backup from prod into development and now want to see a few hours later on what tables the most growth is happening on…
What we’re doing…
This query loops through all of the tables located in the local database where you are running the query from. As it loops, it gets rowcounts for each table, and then attempts to check a remote database for the rowcounts of an equivalent table. This is cool to see in many scenarios. In my case, I wanted to see table activity differences between a development database that I had restored from production and the working production database. I could tell within that day what the most actively used tables were for that day.
You’ll have to use sp_ExecuteSQL to run your dynamically created SELECT statements and then have it populate multiple return values.
Also note that the query has a TRY-CATCH block around the remote database query. This is in case the linked server does not have the proper permissions to query a particular remote table.
DECLARE @NAME VARCHAR(100) DECLARE @SQL NVARCHAR(MAX) DECLARE @ParmDefinition nvarchar(500); DECLARE @s1 nvarchar(1000); DECLARE @s2 nvarchar(1000); CREATE TABLE #COMPARISONTABLE( Id INT IDENTITY , TableName VARCHAR(100) , LocalRowCnt INT , RemoteRowCnt INT , RowDiff INT ) SET @ParmDefinition = N'@s2 nvarchar(1000) OUTPUT,@s1 nvarchar(1000) OUTPUT'; DECLARE CUR CURSOR FOR SELECT NAME FROM SYS.TABLES WHERE TYPE = 'U' AND SCHEMA_ID = 1 OPEN CUR FETCH NEXT FROM CUR INTO @NAME WHILE @@FETCH_STATUS = 0 BEGIN SET @s1=NULL SET @s2=NULL SET @sql = 'SELECT @s1=CAST(COUNT(*) AS NVARCHAR(1000)),@s2='''+@NAME+ ''' FROM MyDatabase.DBO.'+@NAME EXECUTE sp_executesql @sql, @ParmDefinition, @S1 = @s1 OUTPUT,@S2 = @s2 OUTPUT; INSERT INTO #COMPARISONTABLE (TableName,LocalRowCnt) VALUES (@s2,@s1) SET @s1=NULL SET @s2=NULL BEGIN TRY SET @sql = 'SELECT @s1=CAST(COUNT(*) AS NVARCHAR(1000)),@s2='''+@NAME+ ''' FROM XSVRRemoteServer.MyDatabase. DBO.'+@NAME EXECUTE sp_executesql @sql, @ParmDefinition, @S1 = @s1 OUTPUT,@S2 = @s2 OUTPUT; UPDATE #COMPARISONTABLE SET RemoteRowCnt=@s1 WHERE TableName=@s2 UPDATE #COMPARISONTABLE SET RowDiff=(RemoteRowCnt- LocalRowCnt) WHERE TableName=@s2 END TRY BEGIN CATCH -- Access error reading from remote table DELETE FROM #COMPARISONTABLE WHERE TableName=@NAME END CATCH; FETCH NEXT FROM CUR INTO @NAME END CLOSE CUR DEALLOCATE CUR SELECT * FROM #COMPARISONTABLE DROP TABLE #COMPARISONTABLE