The below SQL script returns a list of the table sizes in a given database in descending size order.
SET NOCOUNT ON
CREATE TABLE #TableSizes
INSERT #TableSizes EXEC sp_msForEachTable 'EXEC sp_spaceused ''?'''
CAST(rows as int) as 'num rows',
CAST (SUBSTRING(reserved,0,LEN(reserved)-2) as int) as 'reserved (KB)',
CAST (SUBSTRING(data,0,LEN(data)-2) as int) as 'data (KB)',
CAST (SUBSTRING(index_size,0,LEN(index_size)-2) as int) as 'index_size (KB)',
CAST (SUBSTRING(unused,0,LEN(unused)-2) as int) as 'unused (KB)'
order by [reserved (KB)] desc
DROP TABLE #TableSizes
Below is an example output from the above select statement.
- name = table name
- num rows = number of data rows in table
- reserved = size of the table on disk in KB (data + index + unused)
- data = amount of storage needed for the data portion of the table in KB
- index_size = amount of storage needed for all the associated indexes on the table in KB
- unused = amount of free space available in table in KB