SQLServer 中的表和索引大小

EXEC sp_MSforeachtable @command1="EXEC sp_spaceused '?'"

There is an extended stored procedure sp_spaceused that gets this information out. It's fairly convoluted to do it from the data dictionary, but This link fans out to a script that does it. This stackoverflow question has some fan-out to information on the underlying data structures that you can use to construct estimates of table and index sizes for capcity planning.

The exec sp_spaceused without parameter shows the summary for the whole database. The foreachtable solution generates one result set per table - which SSMS might not be able to handle if you have too many tables.

I created a script which collects the table infos via sp_spaceused and displays a summary in a single record set, sorted by size.

create table #t
(
name nvarchar(128),
rows varchar(50),
reserved varchar(50),
data varchar(50),
index_size varchar(50),
unused varchar(50)
)


declare @id nvarchar(128)
declare c cursor for
select '[' + sc.name + '].[' + s.name + ']' FROM sysobjects s INNER JOIN sys.schemas sc ON s.uid = sc.schema_id where s.xtype='U'


open c
fetch c into @id


while @@fetch_status = 0 begin


insert into #t
exec sp_spaceused @id


fetch c into @id
end


close c
deallocate c


select * from #t
order by convert(int, substring(data, 1, len(data)-3)) desc


drop table #t

sp_spaceused gives you the size of all the indexes combined.

If you want the size of each index for a table, use one of these two queries:

SELECT
i.name                  AS IndexName,
SUM(s.used_page_count) * 8   AS IndexSizeKB
FROM sys.dm_db_partition_stats  AS s
JOIN sys.indexes                AS i
ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id
WHERE s.[object_id] = object_id('dbo.TableName')
GROUP BY i.name
ORDER BY i.name


SELECT
i.name              AS IndexName,
SUM(page_count * 8) AS IndexSizeKB
FROM sys.dm_db_index_physical_stats(
db_id(), object_id('dbo.TableName'), NULL, NULL, 'DETAILED') AS s
JOIN sys.indexes AS i
ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id
GROUP BY i.name
ORDER BY i.name

The results are usually slightly different but within 1%.

On SQL 2012 getting this information on a table level has become deliciously simple:

SQL Management Studio -> Right click on Db -> Reports -> Standard Reports -> Disk usage by table !

Enjoy

Here is more compact version of the most successful answer:

create table #tbl(
name nvarchar(128),
rows varchar(50),
reserved varchar(50),
data varchar(50),
index_size varchar(50),
unused varchar(50)
)


exec sp_msforeachtable 'insert into #tbl exec sp_spaceused [?]'


select * from #tbl
order by convert(int, substring(data, 1, len(data)-3)) desc


drop table #tbl

It’s been a long time since the creation of this post, but I wanted to share my script:

WITH CteIndex
AS
(
SELECT
reservedpages = (reserved_page_count)
,usedpages = (used_page_count)
,pages = (
CASE
WHEN (s.index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)
ELSE lob_used_page_count + row_overflow_used_page_count
END
)
,s.object_id
,i.index_id
,i.type_desc AS IndexType
,i.name AS indexname
FROM sys.dm_db_partition_stats s
INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id
)
SELECT DISTINCT
DB_NAME(DB_ID()) AS DatabaseName
,o.name AS TableName
,o.object_id
,ct.indexname
,ct.IndexType
,ct.index_id
, IndexSpace = LTRIM (STR ((CASE WHEN usedpages > pages THEN CASE WHEN ct.index_id < 2 THEN  pages ELSE (usedpages - pages) END ELSE 0 END) * 8, 15, 0) + ' KB')
FROM CteIndex ct
INNER JOIN sys.objects o ON o.object_id = ct.object_id
INNER JOIN sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL , NULL) ps ON ps.object_id = o.object_id
AND ps.index_id = ct.index_id
ORDER BY name ASC

This works for :

• SQL Server (starting with 2008)
• Includes info for all tables per current database

--Gets the size of each index for the specified table
DECLARE @TableName sysname = N'SomeTable';


SELECT i.name AS IndexName
,8 * SUM(s.used_page_count) AS IndexSizeKB
FROM sys.indexes AS i
INNER JOIN sys.dm_db_partition_stats AS s
ON i.[object_id] = s.[object_id] AND i.index_id = s.index_id
WHERE s.[object_id] = OBJECT_ID(@TableName, N'U')
GROUP BY i.name
ORDER BY i.name;


SELECT i.name AS IndexName
,8 * SUM(a.used_pages) AS IndexSizeKB
FROM sys.indexes AS i
INNER JOIN sys.partitions AS p
ON i.[object_id]  = p.[object_id] AND i.index_id = p.index_id
INNER JOIN sys.allocation_units AS a
ON p.partition_id = a.container_id
WHERE i.[object_id] = OBJECT_ID(@TableName, N'U')
GROUP BY i.name
ORDER BY i.name;

This query comes from two other answers:

Get size of all tables in database

How to find largest objects in a SQL Server database?

, but I enhanced this to be universal. It uses sys.objects dictionary:

SELECT
s.NAME as SCHEMA_NAME,
t.NAME AS OBJ_NAME,
t.type_desc as OBJ_TYPE,
i.name as indexName,
sum(p.rows) as RowCounts,
sum(a.total_pages) as TotalPages,
sum(a.used_pages) as UsedPages,
sum(a.data_pages) as DataPages,
(sum(a.total_pages) * 8) / 1024 as TotalSpaceMB,
(sum(a.used_pages) * 8) / 1024 as UsedSpaceMB,
(sum(a.data_pages) * 8) / 1024 as DataSpaceMB
FROM
sys.objects t
INNER JOIN
sys.schemas s ON t.SCHEMA_ID = s.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
i.OBJECT_ID > 255 AND
i.index_id <= 1
GROUP BY
s.NAME, t.NAME, t.type_desc, i.object_id, i.index_id, i.name
ORDER BY
sum(a.total_pages) DESC
;

To see a single table's (and its indexes) storage data:

exec sp_spaceused MyTable