使用一個T-SQL語句批量查詢資料表佔用空間及其行數

 

要找到資料庫中資料表佔用的空間和存在的行數。可以使用sp_spaceused搭配資料表的名稱。就可以產生該表耗用的空間和現有行數。

如：

USE ADVENTUREWORKS

GO

EXEC sp_spaceused [Sales.SalesOrderHeader]

GO

 

 

但如果資料庫中包含數千的資料表，如何能利用一句SQL語句來實現？

 

解決方法：

一、動態SQL:

先用T-SQL動態產生表示式，然後放到一個查詢中執行。如：

USE ADVENTUREWORKS

GO

SET NOCOUNT ON

SELECT 'EXEC SP_SPACEUSED ['+S.name+'.'+T. name +'];'

FROM sys.tables T INNER JOIN sys.schemas S

ON T.SCHEMA_ID=S.SCHEMA_ID

WHERE S.NAME='HumanResources'

SET NOCOUNT OFF

結果如下：

把結果複製到新的視窗執行即可得到結果。

但這種方法需要人手操作不適合自動化、定時化操作。

二、使用累加字串的方式動態生成：

因為要自動化，所以會利用資料表的INSERT觸發器，執行動態表示式。並且自動將輸入的資料表，計算結果：

--建立表，執行insert觸發器

USE AdventureWorks

GO

CREATE TABLE myTab

(

    TableName VARCHAR(255)

)

GO

 

 

--建立觸發器：

CREATE TRIGGER tr2 ON myTab

AFTER INSERT

AS

    DECLARE @sql VARCHAR(max)

    SET @sql=''

        --使用累加字串，產生語句

    SELECT @sql=@sql+

                        'EXEC sp_spaceused ['+TableName+']; '

            FROMinserted

            --利用EXECUTE 執行動態語句

    EXEC (@sql)

 

--新增指定的資料表名稱，會自動顯示資料表的使用空間：

INSERT myTab

SELECT S.name+'.'+T.name

FROM sys.tables T INNER JOIN sys.schemas S

ON T.schema_id=S.schema_id

WHERE S.name='HumanResources'