Table and Index size in SQL Server

Question

Can we have a SQL query which will basically help in viewing table and index sizes in SQl Server.

How SQL server maintains memory usage for tables/indexes?

Answer 1

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

Answer 2

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.

Answer 3

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.

Answer 4

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,
    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')
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%.

Answer 5

You may also find the stored procedure sp_helpdb useful

Answer 6

Please check this link:http://praveenbattula.blogspot.com/2010/10/find-table-size-used-in-sql-server.html