一、背景
之前寫了篇關於:SQL Server 游標運用:查看一個數據庫所有表大小信息(Sizes of All Tables in a Database)的文章,它羅列出某個數據所有表的信息,這些信息包括:表的記錄數、數據記錄占用空間、索引占用空間、沒使用的空間等(如Figure1所示),現在我來講述如何獲取整個數據庫實例中所有數據庫所有表的信息(如Figure2所示)。
(Figure1:某數據庫所有表信息)
(Figure2:所有數據庫所有表信息)
二、實現方法
下面內容講述了在實現Figure2過程中遇到的一些問題,如果你對這些問題不感興趣可以直接看最後實現的SQL腳本。下面講述了4種實現方法:
1. 游標 + 系統存儲過程sp_MSForEachDB,實現腳本為Script3;
2. 封裝sp_MSforeachtable + sys.databases,實現腳本為Script4和Script5;
3. 系統存儲過程sp_MSForEachDB + sp_MSforeachtable,實現腳本為Script6;
4. 擴展sp_MSforeachdb + sp_MSforeachtable,實現腳本為Script7;
(一) 我們在SQL Server 游標運用:查看一個數據庫所有表大小信息(Sizes of All Tables in a Database)的SQL腳本中進行改進,結合sp_MSForEachDB系統存儲過程進行實現:
1) 既然有了獲取某個數據庫所有表信息的腳本,那就可以在外層再套使用sp_MSForEachDB系統存儲過程,下面的Script1腳本可以獲取到所有數據庫的所有表的信息,效果如Figure3所示:
--Script1: --查看所有數據庫所有表信息 EXEC sp_MSForEachDB 'USE [?]; DECLARE @tablespaceinfo TABLE ( nameinfo VARCHAR(50), rowsinfo INT, reserved VARCHAR(20), datainfo VARCHAR(20), index_size VARCHAR(20), unused VARCHAR(20) ) DECLARE @tablename VARCHAR(255); DECLARE Info_cursor CURSOR FOR SELECT ''[''+[name]+'']'' FROM sys.tables WHERE TYPE=''U''; OPEN Info_cursor FETCH NEXT FROM Info_cursor INTO @tablename WHILE @@FETCH_STATUS = 0 BEGIN INSERT INTO @tablespaceinfo EXEC sp_spaceused @tablename FETCH NEXT FROM Info_cursor INTO @tablename END CLOSE Info_cursor DEALLOCATE Info_cursor SELECT * FROM @tablespaceinfo ORDER BY Cast(Replace(reserved,''KB'','''') AS INT) DESC'