CREATE TABLE #SpaceUsed
( [name] sysname,
rows bigint,
reserved sysname,
data sysname,
index_size sysname,
unused sysname,
reserved_space bigint,
data_space bigint,
index_space bigint,
unused_space bigint)
DECLARE @Counter INT
DECLARE @Max INT
DECLARE @Table sysname
SELECT [name], IDENTITY(INT,1,1) ROWID, schema_id INTO #TableCollection
FROM sys.objects
WHERE type = 'U'
ORDER BY LOWER([name])
UPDATE #TableCollection
SET [name] = schema_Name(schema_id) + '.' + [name]
--select * from #tablecollection
SET @Counter = 1
SET @Max = ( SELECT MAX(ROWID) FROM #TableCollection)
WHILE (@Counter <= @Max)
BEGIN
SET @Table = (
SELECT [name]
FROM #TableCollection
WHERE ROWID = @Counter)
INSERT INTO #SpaceUsed ([name], [rows], reserved, data, index_size, unused)
EXECUTE sp_spaceused @Table, @updateusage = N'FALSE'
SET @Counter = @Counter + 1
END
UPDATE #spaceUsed
SET reserved_space = CAST(REPLACE(reserved, ' KB', '')AS bigint)/1024
UPDATE #spaceUsed
SET data_space = CAST(REPLACE(data, ' KB', '')AS bigint)/1024
UPDATE #spaceUsed
SET index_space = CAST(REPLACE(index_size, ' KB', '')AS bigint)/1024
UPDATE #spaceUsed
SET unused_space = CAST(REPLACE(unused, ' KB', '')AS bigint)/1024
SELECT [name], [rows], reserved_space, data_space, index_space, unused_space
FROM #SpaceUsed
ORDER BY reserved_space DESC
DROP TABLE #TableCollection
DROP TABLE #SpaceUsed