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
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.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments