在
How to measure table size in GB in a table in SQL讨论之后,我正在寻找一种解决方案,使用存储过程sp_spaceused单独测量sql Server所有表所使用的空间.
解决方法
以下基本查询有效.它使用相同的算法获得与sp_spaceused相同的输出,但效率更高.请不要使用CURSOR sp_spaceused方法;绝对没有理由这样做.使用sp_spaceused的一个潜在问题是它应该是一个报告proc,因此输出是所有文本,而不是实际数字,并且将其重新分解为数字可能容易出错.
最好不要使用sys.tables或sp_msforeachtable,因为它们都排除了索引视图.
以下内容与sp_spaceused完全相同：
>包括XML索引,FullText索引,索引视图等.
>细分使用的数据与索引空间的信息
如果您需要它适用于所有数据库,它也可以很容易地适应它.
如果您需要按索引细分这些数据,我在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],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
--so.[name] LIKE N'' -- optional name filter
--ORDER BY ??
