sp_spaceused - 如何在 SQL 中测量所有表中的大小（以 GB 为单位）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26179417/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
sp_spaceused - How to measure the size in GB in all the tables in SQL
提问by Nicolas C
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.
在如何测量 SQL 表中的表大小（以 GB 为单位）中的讨论之后，我正在寻找一种解决方案来使用存储过程单独测量 SQL Server 的所有表使用的空间sp_spaceused。
回答by Solomon Rutzky
The following base query works. It gets the same output as sp_spaceused, using the same algorithm, but muchmore efficiently. Please do not use the CURSOR+ sp_spaceusedmethod; there is absolutely no reason to do that. And a potential problem with using sp_spaceusedis 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.
以下基本查询有效。它得到的输出相同sp_spaceused，使用相同的算法，但很多更有效。请不要使用CURSOR+sp_spaceused方法；绝对没有理由这样做。使用的一个潜在问题sp_spaceused是它旨在成为报告过程，因此输出都是文本，而不是实际数字，并且将其解析回数字可能容易出错。
It is also best to not use either sys.tablesor sp_msforeachtableas they both exclude indexed views.
最好不要使用其中任何一个sys.tables，sp_msforeachtable因为它们都排除索引视图。
The following is exactly the same as sp_spaceused in terms of:
以下内容与 sp_spaceused 完全相同：
- Includes XML indexes, FullText indexes, indexed views, etc.
- Breaks down the info for Data vs Index space used
- 包括 XML 索引、全文索引、索引视图等。
- 分解使用的数据与索引空间的信息
If you need it to work for all databases, it can be easily adapted for that as well.
如果您需要它适用于所有数据库，它也可以轻松适应。
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
如果您需要按索引细分此数据，我已调整以下查询以回答 DBA.StackExchange 上的此问题：sys.allocation_units 和 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
--so.[name] LIKE N'' -- optional name filter
--ORDER BY ??
回答by Dbloch
I know this isn't exactly what you are asking for as it DOESN'T use sp_spaceused but this will provide the results you are after.
我知道这不是您所要求的，因为它不使用 sp_spaceused 但这将提供您所追求的结果。
SELECT
s.Name AS SchemaName,
t.NAME AS TableName,
p.rows AS RowCounts,
SUM(a.total_pages) * 8 AS TotalSpaceKB,
( SUM(a.total_pages) * 8 ) / 1024.0 AS TotalSpaceMB,
(( SUM(a.total_pages) * 8 ) / 1024.0)/1024.0 AS TotalSpaceGB,
SUM(a.used_pages) * 8 AS UsedSpaceKB,
( SUM(a.used_pages) * 8 ) / 1024.0 AS UsedSpaceMB,
(( SUM(a.used_pages) * 8 ) / 1024.0) /1024.0 AS UsedSpaceGB,
( SUM(a.total_pages) - SUM(a.used_pages) ) * 8 AS UnusedSpaceKB,
( ( SUM(a.total_pages) - SUM(a.used_pages) ) * 8 ) / 1024.0 AS UnusedSpaceMB,
(( ( SUM(a.total_pages) - SUM(a.used_pages) ) * 8 ) / 1024.0)/1024.0 AS UnusedSpaceGB,
GROUPING(t.Name)
FROM sys.tables t
INNER JOIN sys.schemas s ON s.schema_id = t.schema_id
INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID
AND i.index_id = p.index_id
INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
WHERE t.NAME NOT LIKE 'dt%'
AND t.is_ms_shipped = 0
AND i.OBJECT_ID > 255
GROUP BY s.Name,
t.Name,
p.Rows
WITH ROLLUP
ORDER BY s.Name,
t.Name
Let me know if you really need it to use sp_spaceused.
如果您真的需要它来使用 sp_spaceused，请告诉我。
回答by Jon Egerton
There are a couple of options here:
这里有几个选项：
sp_msforeachtable
sp_msforeachtable
sp_msforeachtable 'exec sp_spaceused [?]'
While sp_msforeachtable can be used for this is has a couple of limitations:
虽然 sp_msforeachtable 可用于此目的，但有一些限制：
- Firstly you end up with a result set for each table that is run, which is hard to work with
- If you run it over too many tables then you hit the limit of result sets that SSMS will support (think this is usually around the 200 mark)
- 首先，您最终会得到每个运行表的结果集，这很难处理
- 如果你在太多的表上运行它，那么你就会达到 SSMS 支持的结果集的限制（认为这通常在 200 左右）
Collated results
整理结果
With a little more work, we can collate all the results into a single data set, avoiding these limitations. The pattern of this solution is similar whenever there is a requirement to run a sproc a bunch of times with different parameters and to collate the results from those runs.
只需多做一点工作，我们就可以将所有结果整理到一个数据集中，从而避免这些限制。当需要使用不同的参数多次运行 sproc 并整理这些运行的结果时，此解决方案的模式是相似的。
--Get list of interesting tables
declare @tables table(id int identity(1,1), name varchar(200))
declare @tablename varchar(200)
insert @tables
select table_name from information_schema.tables where table_type = 'BASE TABLE'
--Define table for results
declare @info table(name varchar(200), rows bigint, reserved varchar(200), data varchar(200), index_size varchar(200), unused varchar(200))
--Working vars
declare @max int, @pos int
select @max = count(1), @pos = 1 from @tables
--Loop to get all results
while @pos <= @max
begin
select @tablename = name from @tables where id = @pos
insert @info
exec sp_spaceused @tablename
set @pos = @pos + 1
end
--return all results
select * from @info
I prefer to use variable tables for this, but a cursor/temp table can be used as well.
我更喜欢为此使用变量表，但也可以使用游标/临时表。
回答by Arun
I'm assuming your trying to use sp_spaceused to find the size for ALL tables in a db?
我假设您尝试使用 sp_spaceused 来查找数据库中所有表的大小？
If so, you can do this:
如果是这样，你可以这样做：
USE database_name
GO
sp_msforeachtable 'exec sp_spaceused [?]'
