問題描述

按照如何在 SQL 中的表中以 GB 為單位測量表大小 中的討論，我正在尋找解決方案使用存儲過程 sp_spaceused 單獨測量 SQL Server 的所有表使用的空間.

Following the discussion in How to measure table size in GB in a table in SQL, I'm looking for a solution to measure the space used by all the tables of a SQL Server individually using the store procedure sp_spaceused.

推薦答案

以下基本查詢有效.它使用相同的算法獲得與 sp_spaceused 相同的輸出，但效率更高.請不要使用CURSOR + sp_spaceused 方法；絕對沒有理由這樣做.使用 sp_spaceused 的一個潛在問題是它旨在成為一個報告過程，因此輸出都是文本，而不是實際數字，并且將其解析回數字可能容易出錯.

The following base query works. It gets the same output as sp_spaceused, using the same algorithm, but much more efficiently. Please do not use the CURSOR + sp_spaceused method; there is absolutely no reason to do that. And a potential problem with using sp_spaceused is that it is intended to be a report proc so the output is all text, not actual numbers, and parsing that back into numbers can be error-prone.

最好不要使用 sys.tables 或 sp_msforeachtable，因為它們都排除索引視圖.

It is also best to not use either sys.tables or sp_msforeachtable as they both exclude indexed views.

以下和 sp_spaceused 完全一樣:

The following is exactly the same as sp_spaceused in terms of:

• 包括 XML 索引、全文索引、索引視圖等.
• 細分數據與索引空間使用的信息

如果您需要它適用于所有數據庫，它也可以輕松適應.

If you need it to work for all databases, it can be easily adapted for that as well.

如果您需要按索引細分此數據，我已調整以下查詢以回答 DBA.StackExchange 上的此問題:sys.allocation_units 和 sp_spaceused 上的空間使用情況

If you need this data broken down per index, I have adapted the following query in response to this question on DBA.StackExchange: space usage on sys.allocation_units and sp_spaceused

;WITH extra AS
(   -- Get info for FullText indexes, XML Indexes, etc
    SELECT  sit.[object_id],
            sit.[parent_id],
            ps.[index_id],
            SUM(ps.reserved_page_count) AS [reserved_page_count],
            SUM(ps.used_page_count) AS [used_page_count]
    FROM    sys.dm_db_partition_stats ps
    INNER JOIN  sys.internal_tables sit
            ON  sit.[object_id] = ps.[object_id]
    WHERE   sit.internal_type IN
               (202, 204, 207, 211, 212, 213, 214, 215, 216, 221, 222, 236)
    GROUP BY    sit.[object_id],
                sit.[parent_id],
                ps.[index_id]
), agg AS
(   -- Get info for Tables, Indexed Views, etc (including "extra")
    SELECT  ps.[object_id] AS [ObjectID],
            ps.index_id AS [IndexID],
            SUM(ps.in_row_data_page_count) AS [InRowDataPageCount],
            SUM(ps.used_page_count) AS [UsedPageCount],
            SUM(ps.reserved_page_count) AS [ReservedPageCount],
            SUM(ps.row_count) AS [RowCount],
            SUM(ps.lob_used_page_count + ps.row_overflow_used_page_count)
                    AS [LobAndRowOverflowUsedPageCount]
    FROM    sys.dm_db_partition_stats ps
    GROUP BY    ps.[object_id],
                ps.[index_id]
    UNION ALL
    SELECT  ex.[parent_id] AS [ObjectID],
            ex.[object_id] AS [IndexID],
            0 AS [InRowDataPageCount],
            SUM(ex.used_page_count) AS [UsedPageCount],
            SUM(ex.reserved_page_count) AS [ReservedPageCount],
            0 AS [RowCount],
            0 AS [LobAndRowOverflowUsedPageCount]
    FROM    extra ex
    GROUP BY    ex.[parent_id],
                ex.[object_id]
), spaceused AS
(
SELECT  agg.[ObjectID],
        OBJECT_SCHEMA_NAME(agg.[ObjectID]) AS [SchemaName],
        OBJECT_NAME(agg.[ObjectID]) AS [TableName],
        SUM(CASE
                WHEN (agg.IndexID < 2) THEN agg.[RowCount]
                ELSE 0
            END) AS [Rows],
        SUM(agg.ReservedPageCount) * 8 AS [ReservedKB],
        SUM(agg.LobAndRowOverflowUsedPageCount +
            CASE
                WHEN (agg.IndexID < 2) THEN (agg.InRowDataPageCount)
                ELSE 0
            END) * 8 AS [DataKB],
        SUM(agg.UsedPageCount - agg.LobAndRowOverflowUsedPageCount -
            CASE
                WHEN (agg.IndexID < 2) THEN agg.InRowDataPageCount
                ELSE 0
            END) * 8 AS [IndexKB],
        SUM(agg.ReservedPageCount - agg.UsedPageCount) * 8 AS [UnusedKB],
        SUM(agg.UsedPageCount) * 8 AS [UsedKB]
FROM    agg
GROUP BY    agg.[ObjectID],
            OBJECT_SCHEMA_NAME(agg.[ObjectID]),
            OBJECT_NAME(agg.[ObjectID])
)
SELECT sp.SchemaName,
       sp.TableName,
       sp.[Rows],
       sp.ReservedKB,
       (sp.ReservedKB / 1024.0 / 1024.0) AS [ReservedGB],
       sp.DataKB,
       (sp.DataKB / 1024.0 / 1024.0) AS [DataGB],
       sp.IndexKB,
       (sp.IndexKB / 1024.0 / 1024.0) AS [IndexGB],
       sp.UsedKB AS [UsedKB],
       (sp.UsedKB / 1024.0 / 1024.0) AS [UsedGB],
       sp.UnusedKB,
       (sp.UnusedKB / 1024.0 / 1024.0) AS [UnusedGB],
       so.[type_desc] AS [ObjectType],
       so.[schema_id] AS [SchemaID],
       sp.ObjectID
FROM   spaceused sp
INNER JOIN sys.all_objects so
        ON so.[object_id] = sp.ObjectID
WHERE so.is_ms_shipped = 0
--AND so.[name] LIKE N''  -- optional name filter
--ORDER BY ??

這篇關于sp_spaceused - 如何在 SQL 中測量所有表中的大小(以 GB 為單位)的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！