查询SQL数据库中各数据表的空间使用情况

--查询数据库中各数据表的空间使用情况
CREATE VIEW vTableDataUse
AS
    SELECT TOP 1000 
    a3.name AS [schemaname],
    a2.name AS [tablename],
    a1.rows as row_count,
    (a1.reserved + ISNULL(a4.reserved,0))* 8 AS [reserved(K)],
    a1.data * 8 AS [data(k)],
    (CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN (a1.used + ISNULL(a4.used,0)) - a1.data ELSE 0 END) * 8 AS [index_size(k)],
    (CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END) * 8 AS [unused(k)],
    a1.data * 8*1024/(CASE WHEN a1.Rows=0 THEN 1 ELSE a1.Rows END) BytesPerRow
    FROM (SELECT ps.object_id,
        SUM (CASE WHEN (ps.index_id < 2) THEN row_count ELSE 0 END) AS [rows],
        SUM (ps.reserved_page_count) AS reserved,
        SUM (CASE WHEN (ps.index_id < 2) THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count)
        ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count) END) AS data,
        SUM (ps.used_page_count) AS used 
        FROM sys.dm_db_partition_stats ps GROUP BY ps.object_id) AS a1 
        LEFT OUTER JOIN(
        SELECT it.parent_id,
            SUM(ps.reserved_page_count) AS reserved,
            SUM(ps.used_page_count) AS used 
            FROM sys.dm_db_partition_stats ps INNER JOIN sys.internal_tables it ON (it.object_id = ps.object_id
           ) WHERE it.internal_type IN (202,204)GROUP BY it.parent_id) AS a4 
            ON (a4.parent_id = a1.object_id)
    INNER JOIN sys.all_objects a2 ON (a1.object_id = a2.object_id ) 
    INNER JOIN sys.schemas a3 ON (a2.schema_id = a3.schema_id) 
    WHERE a2.type <> N'S' and a2.type <> N'IT' and a3.schema_id=1
    ORDER BY [reserved(K)] DESC

GO

Select * from vTableDataUse