/*
SchemeName TableName RowsInTable ReservedSpace DataSpace IndexSpace
Sales SalesDet 121317 15752 KB 9880 KB 5312 KB
Prod TransHist 113443 9920 KB 6304 KB 3192 KB
...
*/
DECLARE @cn int, @m int, @t sysname, @sn varchar(256), @fn varchar(256)
DECLARE @spsUsed TABLE (s sysname NULL DEFAULT NULL, n nvarchar(128), rs char(11), rsr varchar(18), dt varchar(18), idxvarchar(18), uns varchar(18))
DECLARE @tables TABLE (n sysname, s int, id int IDENTITY)
SET NOCOUNT ON
INSERT INTO @tables SELECT name, schema_id FROM sys.objects WHERE type = 'U'
SET @m=@@ROWCOUNT
SET @cn = 1
WHILE @cn <= @m
BEGIN
SET @t = (SELECT n FROM @tables WHERE id=@cn)
SET @sn = SCHEMA_NAME((SELECT s FROM @tables WHERE id=@cn))
SET @fn=@sn+'.'+@t
INSERT INTO @spsUsed (n,rs,rsr,dt,idx,uns) EXECUTE sp_spaceused @fn
UPDATE @spsUsed SET s=@sn WHERE s IS NULL
SET @cn = @cn + 1
END
SELECT s AS SchemeName, n AS TableName, rs AS RowsInTable, rsr AS ReservedSpace, dt AS DataSpace, idx AS IndexSpace, uns ASNotYetUsedSpace FROM @spsUsed
ORDER BY CONVERT(int, rs) DESC
SET NOCOUNT OFF
