SQL - 如何使用 T-SQL 來顯示資料庫中資料表空間使用情形?

  • Article

有些朋友詢問：如何使用 T-SQL 來知道資料庫中資料表空間使用情形？

可以使用下列的範例來查詢，也可以再搭配排程及 e-mail 的功能，找擴充它的功能。

當然也可以再寫的更複雜一些，讓它自動查詢整台 SQL Server 中所有的資料庫中的所有資料表的使用情形。

-- 此範例也支援中文的資料庫名稱及中文的資料表名稱

IF exists (SELECT * FROM sys.objects WHERE object_id = object_id(N'[dbo].[DisplayDatabaseSpaceUsed]') and OBJECTPROPERTY(object_id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[DisplayDatabaseSpaceUsed]
GO

CREATE PROCEDURE DisplayDatabaseSpaceUsed
@SourceDB    NVARCHAR(254)
AS
SET NOCOUNT ON
DECLARE @sql NVARCHAR(500)
    CREATE TABLE #tables(tableName NVARCHAR(254))
    SELECT @sql = N'INSERT #tables SELECT TABLE_NAME FROM [' + @SourceDB + '].INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = ''BASE TABLE'''
    EXEC (@sql)
    CREATE TABLE #SpaceUsed (tableName NVARCHAR(254), rows varchar(11), reserved varchar(18), data varchar(18), index_size varchar(18), unused varchar(18))
    DECLARE @tableName NVARCHAR(254)
    SELECT @tableName = N''
    WHILE EXISTS (SELECT * FROM #tables WHERE tableName > @tableName)
    BEGIN
        SELECT @tableName = MIN(tableName) FROM #tables WHERE tableName > @tableName
        SELECT @sql = 'EXEC ' + @SourceDB + '..SP_EXECUTESQL N''INSERT #SpaceUsed EXEC SP_SPACEUSED [' + @tableName + ']'''
        EXEC (@sql)
    end
    SELECT * FROM #SpaceUsed
    DROP TABLE #tables
    DROP TABLE #SpaceUsed
GO
/*

-- 請修改資料庫名稱
EXEC DisplayDatabaseSpaceUsed N'中文資料庫名稱'
EXEC DisplayDatabaseSpaceUsed 'Northwind'
*/

執行結果：

image