Sql Server – Listing all tables Metadata (Table Size, row count, Index Size)
Suppose you need to find out Table size, row
count , index size etc. for a table. In this case the easiest way to do that is
using sp_spaceused, here is the syntax –
EXEC sp_spaceused 'TableName' Like EXEC sp_spaceused 'DimCustomer'
The output will be something like that –
name
|
rows
|
reserved
|
data
|
index_size
|
unused
|
ItemFlatten
|
259458
|
251464 KB
|
109072 KB
|
141880 KB
|
512 KB
|
Looks good and easy but what if you want the
list of all tables in your database and their metadata. Here is the script for
that -
Begin
IF OBJECT_ID('tempdb..#Temp') IS NOT NULL
/*Then it exists*/
DROP TABLE #Temp
select O.name as 'TableName',O.type_desc,SUM (reserved_page_count) as 'ReservedPages',
SUM (used_page_count) as 'Usedpages',
SUM (
CASE
WHEN (index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)
ELSE 0
END
) as Pages,
SUM (
CASE
WHEN (index_id < 2) THEN row_count
ELSE 0
END
) as Row_Count
Into #Temp
from Sys.objects O
Inner join sys.dm_db_partition_stats S on O.object_id=S.object_id and O.type in ('U')
Group By O.name,O.type_desc
SELECT
name = TableName,
type_desc,
rows = convert (char(20), Row_Count),
reserved = LTRIM (STR (ReservedPages * 8, 15, 0) + ' KB'),
data = LTRIM (STR (Pages * 8, 15, 0) + ' KB'),
index_size = LTRIM (STR ((CASE WHEN Usedpages > Pages THEN (Usedpages - Pages) ELSE 0 END) * 8, 15, 0) + ' KB'),
unused = LTRIM (STR ((CASE WHEN ReservedPages > Usedpages THEN (ReservedPages - Usedpages) ELSE 0 END) * 8, 15, 0) + ' KB')
From #Temp
Order By TableName
Drop Table #Temp
End
Output will be something like this –
Please share your feedback and let me know if you feel this
can be improved in anyway.
