[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'...