CREATE TABLE #Data
(
Name VARCHAR (100),
Row VARCHAR (100),
Reserved VARCHAR (100),
Data VARCHAR (100),
Index_size VARCHAR (100),
Unused VARCHAR (100)
);
DECLARE @name VARCHAR (100);
DECLARE cur CURSOR for
SELECT Name
From sysobjects
WHERE xtype = ' u '
ORDER by name;
OPEN cur;
FETCH NEXT from cur
into @name;
While @ @fetch_status = 0
BEGIN
INSERT into #Data
EXEC sp_spaceused @name;
PRINT @name;
FETCH NEXT from cur
into @name;
END;
CLOSE cur;
DEALLOCATE cur;
CREATE TABLE #DataNew
(
Name VARCHAR (100),
Row INT,
Reserved INT,
Data INT,
Index_size INT,
Unused INT
);
INSERT into #DataNew
SELECT name,
CONVERT (INT, row) as row,
CONVERT (INT, REPLACE (reserved, ' KB ', ')) as reserved,
CONVERT (INT, REPLACE (data, ' KB ', ')) as data,
CONVERT (INT, REPLACE (index_size, ' KB ', ')) as Index_size,
CONVERT (INT, REPLACE (Unused, ' KB ', ')) as unused
From #Data;
SELECT *
From #DataNew
ORDER by Data DESC;
DROP TABLE #Data,
#DataNew;
SQL Server View all table sizes, occupied space