TSQL Query to Compare Row Counts of Tables in a Local and a Remote Database

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.

The Code:

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