我希望我的查询返回两个报表服务器上所有表的表名和行计数。它们都有相同的表。另外,前几天我已经在这两者之间添加了链接服务器。
到目前为止查询一台服务器,不确定如何添加与我们的另一台服务器连接的第三列:
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)
期望输出:
TableName DB1_rows DB2_Rows
----------+-----------+-----------
Account | 20,000 | 19,388
Contacts | 1,234 | 1,390
Bla | 2,330 | 2,430
这对于公用表表达式 (CTE) 来说是一个很好的用途,因为您可以运行多个查询,然后将这些查询结果连接在一起并以不同的方式分析/操作它们:
/* 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
您也可以使用APPLY
语句或相关子查询来实现此目的,但使用 CTE 的优点是,您不会为父查询返回的每一行运行子查询。使用 CTE,您可以运行查询,然后简单地将该查询结果视为另一个表。
显然,您需要对此进行测试。我目前无法访问SQL Server,因此此处或那里可能存在拼写错误。
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
只需将 LIVE 和 TEST 以及"SET @SQL"语句第二行上的"dbo"模式名称更改为 2 个数据库的名称。
编辑:您还可以将其中一个数据库名称.schema名称添加到顶部的"从sys.tables选择名称"语句中,以及您想要执行的任何表名称过滤。
如果您能够从一台服务器获取结果,那么如果设置了链接服务器,则可以从另一台服务器获得相同的结果。
您可以使用 4 部分名称来执行此操作。例如:
Select ...
From ServerName.DBName.schema.TableName
...