按照如何在 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:
如果您需要它适用于所有数据库,它也可以轻松适应.
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模板网!