[SQL]Query table space in Database

 

SQL Script :

 

 

DECLARE @tablespaceinfo TABLE ( 
    nameinfo varchar(50), 
    rowsinfo int, 
    reserved varchar(20), 
    datainfo varchar(20), 
    index_size varchar(20), 
    unused varchar(20) 
) 

DECLARE @tablename varchar(255); 
DECLARE Info_cursor CURSOR FOR
    SELECT [name] FROM sys.tables WHERE type='U'; 
OPEN Info_cursor 
FETCH NEXT FROM Info_cursor INTO @tablename 
WHILE @@FETCH_STATUS = 0 
BEGIN
    insert into @tablespaceinfo exec sp_spaceused @tablename 
    FETCH NEXT FROM Info_cursor 
    INTO @tablename 
END

CLOSE Info_cursor 
DEALLOCATE Info_cursor 
SELECT * FROM @tablespaceinfo 
ORDER BY Cast(Replace(reserved,'KB','') as INT) DESC