Count all rows from all tables in two databases on different servers

Question

I would like my query to return the table name, and rowcount for all of the tables on our two reporting servers. They both have the same tables. Also, I already added the linked server the other day between these two.

Query so far for one server, not sure how to add a third column connected with our other server though:

SELECT 
    t.NAME AS TableName,
    p.[Rows]
FROM 
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
WHERE 
    t.NAME NOT LIKE 'dt%' AND
    i.OBJECT_ID > 255 AND   
    i.index_id <= 1
GROUP BY 
    t.NAME, i.object_id, i.index_id, i.name, p.[Rows]
ORDER BY 
    object_name(i.object_id) 

Desired output:

TableName   DB1_rows     DB2_Rows
----------+-----------+-----------
Account   |  20,000   |  19,388
Contacts  |   1,234   |   1,390
Bla       |   2,330   |   2,430

Answer 1

This would be a great use for Common Table Expressions (CTE's) as you can run multiple queries, then join those query results together and analyze/manipulate them in different ways:

/* Use the WITH keyword to start your first expression */
WITH SERVER_A AS (
  SELECT 
      t.NAME AS TableName,
      p.[Rows] AS NumRows
  FROM 
      sys.tables t
  INNER JOIN      
      sys.indexes i ON t.OBJECT_ID = i.object_id
  INNER JOIN 
      sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
  INNER JOIN 
      sys.allocation_units a ON p.partition_id = a.container_id
  WHERE 
      t.NAME NOT LIKE 'dt%' AND
      i.OBJECT_ID > 255 AND   
      i.index_id <= 1
  GROUP BY 
      t.NAME, i.object_id, i.index_id, i.name, p.[Rows]
),

/* Then chain additional expressions (this time adding the linked server into the table name) */
SERVER_B AS (
  SELECT 
      t.NAME AS TableName,
      p.[Rows] AS NumRows
  FROM 
      LINKED_SERVER_NAME.sys.tables t
  INNER JOIN      
      LINKED_SERVER_NAME.sys.indexes i ON t.OBJECT_ID = i.object_id
  INNER JOIN 
      LINKED_SERVER_NAME.sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
  INNER JOIN 
      LINKED_SERVER_NAME.sys.allocation_units a ON p.partition_id = a.container_id
  WHERE 
      t.NAME NOT LIKE 'dt%' AND
      i.OBJECT_ID > 255 AND   
      i.index_id <= 1
  GROUP BY 
      t.NAME, i.object_id, i.index_id, i.name, p.[Rows]
)

/* Then join the two together on a common column */
SELECT
  A.TableName,
  A.NumRows AS DB1_Rows,
  B.NumRows AS DB2_Rows

FROM SERVER_A A
  LEFT JOIN SERVER_B B ON
    A.TableName = B.TableName

ORDER BY
  A.TableName ASC

You could also accomplish this with APPLY statements or correlated sub-queries, but the advantage to using a CTE is that you're not running the sub-query for every single row that the parent query returns. Using a CTE you can run a query and then simply treat that query result as if it were a another table.

Obviously you'll want to test this. I don't have access to a SQL Server at the moment, so there may be a typo here or there.

Answer 2

DECLARE @RESULT TABLE (TableName VARCHAR(MAX),   DB1_rows  INT,   DB2_Rows INT)
DECLARE @TABLENAME VARCHAR(MAX), @SQL VARCHAR(MAX)

DECLARE cCursor CURSOR FOR 
SELECT name FROM sys.tables

OPEN cCursor 
FETCH NEXT FROM cCursor INTO @TABLENAME 

WHILE @@FETCH_STATUS = 0
    BEGIN 
        SET @SQL = 'SELECT  ''' + @TABLENAME + ''' , COUNT(*) FROM ' + @TABLENAME

        DECLARE @FirstColumn VARCHAR(MAX) = (SELECT TOP 1 c.name FROM sys.columns c JOIN sys.tables t ON t.object_id = c.object_id WHERE t.name = @TABLENAME ORDER BY column_id)

        SET @SQL = 'SELECT  ''' + @TABLENAME + ''' , SUM(CASE WHEN A.' + @FirstColumn + ' IS NOT NULL THEN 1 ELSE 0 END), SUM(CASE WHEN B.' + @FirstColumn + ' IS NOT NULL THEN 1 ELSE 0 END) '
                   +'FROM LIVE.dbo.' + @TABLENAME + ' AS A FULL JOIN TEST.dbo.' + @TABLENAME + ' AS B on 1=0'

        INSERT INTO @RESULT EXEC (@SQL) 

        FETCH NEXT FROM cCursor INTO @TABLENAME 
    END

CLOSE cCURSOR 
DEALLOCATE cCURSOR

SELECT * FROM @RESULT

Just change the LIVE and TEST and the 'dbo' schema name on the second line of the 'SET @SQL' statement to the names of the 2 databases.

EDIT: Also you can add one of the database names.schema names to the 'SELECT name FROM sys.tables' statement at the top, plus any table name filtering you wanted to do.

Answer 3

If you are able to get the result from one server, then you can get the same from other server if the linked server is setup.

You can do it by using the 4 part name. For example:

Select ...
From ServerName.DBName.schema.TableName
...