SQL Server 統計所有資料表列數：動態 SQL 批次處理

需快速統計資料庫中所有使用者資料表的列數，可利用 sysobjects (或 sys.tables) 搭配動態 SQL 批次執行 COUNT(*)，結果寫入暫存表彙總。

核心思路

1. 從 sysobjects 取得所有 xtype='U' (使用者資料表) 名稱。
2. 動態產生 SELECT 'TableName', COUNT(*) FROM TableName UNION ALL ... 批次語句。
3. 分批執行避免單一語句超過 8000 字元限制。
4. 結果插入暫存表 #temp，最後彙總輸出。

完整腳本

DECLARE @sql varchar(8000), @cnt int, @step int = 50;
SET NOCOUNT ON;

IF OBJECT_ID('tempdb..#temp') IS NOT NULL DROP TABLE #temp;
CREATE TABLE #temp (name sysname, cnt numeric(18));

IF OBJECT_ID('tempdb..#temp1') IS NOT NULL DROP TABLE #temp1;
CREATE TABLE #temp1 (id int IDENTITY(1,1), name sysname);

INSERT #temp1(name) SELECT name FROM sysobjects WHERE xtype = 'U';
SET @cnt = @@ROWCOUNT;

WHILE @cnt > 0
BEGIN
  SET @sql = '';
  SELECT @sql = @sql + ' SELECT ''' + name + ''', COUNT(1) FROM ' + name + ' UNION'
  FROM #temp1
  WHERE id BETWEEN @cnt - @step + 1 AND @cnt;

  SET @sql = LEFT(@sql, LEN(@sql) - 5); -- 移除尾端 UNION
  INSERT #temp EXEC(@sql);

  SET @cnt = @cnt - @step;
END

SELECT name, cnt FROM #temp ORDER BY name;

優化建議

• @step 可依資料表名稱平均長度調整，建議 30~80。
• SQL Server 2005+ 建議改用 sys.tables 與 sys.partitions 直接查詢，無需逐表 COUNT(*)：
  SELECT t.name, SUM(p.rows) FROM sys.tables t JOIN sys.partitions p ON t.object_id = p.object_id WHERE p.index_id IN (0,1) GROUP BY t.name;
• 若僅需估算值，sp_spaceused 或 sys.dm_db_partition_stats 更快。