我继承了一个相当大的SQL Server数据库。考虑到它包含的数据,它似乎比我预期的要占用更多的空间。
是否有一种简单的方法来确定每个表占用的磁盘空间?
我继承了一个相当大的SQL Server数据库。考虑到它包含的数据,它似乎比我预期的要占用更多的空间。
是否有一种简单的方法来确定每个表占用的磁盘空间?
当前回答
我在marc_s答案的顶部添加了几列:
with fs
as
(
select i.object_id,
p.rows AS RowCounts,
SUM(a.total_pages) * 8 AS TotalSpaceKb
from sys.indexes i 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
i.OBJECT_ID > 255
GROUP BY
i.object_id,
p.rows
)
SELECT
t.NAME AS TableName,
fs.RowCounts,
fs.TotalSpaceKb,
t.create_date,
t.modify_date,
( select COUNT(1)
from sys.columns c
where c.object_id = t.object_id ) TotalColumns
FROM
sys.tables t INNER JOIN
fs ON t.OBJECT_ID = fs.object_id
WHERE
t.NAME NOT LIKE 'dt%'
AND t.is_ms_shipped = 0
ORDER BY
t.Name
其他回答
SELECT
t.NAME AS TableName,
s.Name AS SchemaName,
p.rows,
SUM(a.total_pages) * 8 AS TotalSpaceKB,
CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
SUM(a.used_pages) * 8 AS UsedSpaceKB,
CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB,
(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
FROM
sys.tables t
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
LEFT OUTER JOIN
sys.schemas s ON t.schema_id = s.schema_id
WHERE
t.NAME NOT LIKE 'dt%'
AND t.is_ms_shipped = 0
AND i.OBJECT_ID > 255
GROUP BY
t.Name, s.Name, p.Rows
ORDER BY
TotalSpaceMB DESC, t.Name
在上面的@Mark回答中,添加了@updateusage='true'以强制更新最新的尺码统计(https://msdn.microsoft.com/en-us/library/ms188776.aspx):
SET NOCOUNT ON
DECLARE @TableInfo TABLE (tablename varchar(255), rowcounts int, reserved varchar(255), DATA varchar(255), index_size varchar(255), unused varchar(255))
DECLARE @cmd1 varchar(500)
SET @cmd1 = 'exec sp_spaceused @objname =''?'', @updateusage =''true'' '
INSERT INTO @TableInfo (tablename,rowcounts,reserved,DATA,index_size,unused)
EXEC sp_msforeachtable @command1=@cmd1
SELECT * FROM @TableInfo ORDER BY Convert(int,Replace(DATA,' KB','')) DESC
我要感谢Greg Low先生的提问:
SELECT o.name AS ObjectName,
SUM(reserved_page_count) * 8.0 / 1024 AS SizeinMB
FROM sys.dm_db_partition_stats AS ps
INNER JOIN sys.sysobjects AS o
ON ps.object_id = o.id
GROUP BY o.name
ORDER BY SizeinMB DESC;
-- Show the size of all the tables in a database sort by data size descending
SET NOCOUNT ON
DECLARE @TableInfo TABLE (tablename varchar(255), rowcounts int, reserved varchar(255), DATA varchar(255), index_size varchar(255), unused varchar(255))
DECLARE @cmd1 varchar(500)
SET @cmd1 = 'exec sp_spaceused ''?'''
INSERT INTO @TableInfo (tablename,rowcounts,reserved,DATA,index_size,unused)
EXEC sp_msforeachtable @command1=@cmd1
SELECT * FROM @TableInfo ORDER BY Convert(int,Replace(DATA,' KB','')) DESC
经过一番搜索,我找不到一种简单的方法来获取所有表格的信息。有一个名为sp_spaceused的方便存储过程,它将返回数据库使用的所有空间。如果提供了表名,则返回该表使用的空间。但是,存储过程返回的结果不可排序,因为列是字符值。
以下脚本将生成我要查找的信息。
create table #TableSize (
Name varchar(255),
[rows] int,
reserved varchar(255),
data varchar(255),
index_size varchar(255),
unused varchar(255))
create table #ConvertedSizes (
Name varchar(255),
[rows] int,
reservedKb int,
dataKb int,
reservedIndexSize int,
reservedUnused int)
EXEC sp_MSforeachtable @command1="insert into #TableSize
EXEC sp_spaceused '?'"
insert into #ConvertedSizes (Name, [rows], reservedKb, dataKb, reservedIndexSize, reservedUnused)
select name, [rows],
SUBSTRING(reserved, 0, LEN(reserved)-2),
SUBSTRING(data, 0, LEN(data)-2),
SUBSTRING(index_size, 0, LEN(index_size)-2),
SUBSTRING(unused, 0, LEN(unused)-2)
from #TableSize
select * from #ConvertedSizes
order by reservedKb desc
drop table #TableSize
drop table #ConvertedSizes