June 14, 2007 at 2:45 am
Hi there,
does anyone perhaps have a SQL script or query that will give me all the tables sizes within a databases. I know about this script:
EXEC sp_spaceused 'tablename'
I don't want to use it because it only brings back one table size. I want all the table sizes.
Regards
IC
June 14, 2007 at 5:04 am
http://www.sqlservercentral.com/forums/post.aspx?forumid=146&messageid=373676
I have already posted the query there.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
June 14, 2007 at 6:06 am
Hi there,
the total size column of the table, is it in KB?
June 14, 2007 at 6:29 am
thats in KB. you need to sum up the index, data and reserved columns to get the space used by a table.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
June 14, 2007 at 11:59 pm
DROP TABLE aaa
CREATE TABLE aaa
(name varchar(256),
rows varchar(256),
reserved varchar(256),
data varchar(256),
index_size varchar(256),
unused varchar(256),
irows int,
ireserved int,
idata int,
)
declare @tabname as varchar(100)
DECLARE table_cursor CURSOR FOR
SELECT name FROM sys.tables ORDER BY name
OPEN table_cursor
-- Perform the first fetch.
FETCH NEXT FROM table_cursor INTO @tabname
-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
-- This is executed as long as the previous fetch succeeds.
insert into aaa (name, rows, reserved, data, index_size, unused)
exec sp_spaceused @tabname
FETCH NEXT FROM table_cursor INTO @tabname
END
CLOSE table_cursor
DEALLOCATE table_cursor
UPDATE aaa SET
irows = cast((replace(rows, ' KB', '')) as int),
ireserved = cast((replace(reserved, ' KB', '')) as int),
idata = cast((replace(data, ' KB', '')) as int)
SELECT * FROM aaa ORDER BY idata
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply