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.