Friday, October 29, 2010

To find the space occupied by each table in a database

-- Create the temporary table...
CREATE TABLE #tblResults
(
[name] nvarchar(50),
[rows] Bigint,
[reserved] varchar(50),
[reserved_int] int default(0),
[data] varchar(50),
[data_int] int default(0),
[index_size] varchar(50),
[index_size_int] int default(0),
[unused] varchar(18),
[unused_int] int default(0)
)




EXEC sp_MSforeachtable @command1=
"INSERT INTO #tblResults
([name],[rows],[reserved],[data],[index_size],[unused])
EXEC sp_spaceused '?'"

select * from #tblResults order by rows desc

Drop table #tblResults