[SQL] 取得所有資料表的筆數

如果有複數的資料庫及資料表，一個一個下

SELECT COUNT(1) FROM TABLE

也挺麻煩的，所以花了一點時間撰寫 T-SQL 可以一次性抓取所有資料庫的資料表的筆數

語法如下：

DECLARE @TABLES TABLE (
	[TABLE_CATALOG] SYSNAME NOT NULL,
	[TABLE_SCHEMA] SYSNAME NOT NULL,
	[TABLE_NAME] SYSNAME NOT NULL
)
DECLARE @COUNTS TABLE (
	[COUNT] BIGINT NOT NULL,
	[TABLE_CATALOG] SYSNAME NOT NULL,
	[TABLE_SCHEMA] SYSNAME NOT NULL,
	[TABLE_NAME] SYSNAME NOT NULL
)
DECLARE @SQL NVARCHAR(MAX)
SET @SQL = (SELECT STUFF((SELECT N' UNION ALL SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME FROM ' + QUOTENAME(name) + N'.INFORMATION_SCHEMA.TABLES' FROM master.sys.databases WHERE NOT [name] IN (N'master', N'tempdb', N'msdb') FOR XML PATH(N'')), 1, 11, N''))
INSERT @TABLES EXEC sys.sp_executesql @SQL
SET @SQL = (SELECT STUFF((SELECT N' UNION ALL SELECT COUNT(1), N' + QUOTENAME(TABLE_CATALOG, N'''') + ', N' + QUOTENAME(TABLE_SCHEMA, N'''') + ', N' + QUOTENAME(TABLE_NAME, N'''') + ' FROM ' + QUOTENAME(TABLE_CATALOG) + N'.' + QUOTENAME(TABLE_SCHEMA) + N'.' + QUOTENAME(TABLE_NAME) FROM @TABLES FOR XML PATH(N'')), 1, 11, N''))
INSERT @COUNTS EXEC sys.sp_executesql @SQL
SELECT * FROM @COUNTS ORDER BY TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME