--from beirut's blog

--用游标处理 查询每个表的大小并排序
DECLARE
    @TABLENM SYSNAME,
    @CNT INT,
    @TOPN INT
DECLARE TABLE_SPACE CURSOR FAST_FORWARD
    FOR 
         SELECT
         NAME
         FROM SYSOBJECTS
         WHERE XTYPE = 'U'
SELECT @CNT = 0, @TOPN = 0
CREATE TABLE #TMPUSAGE
     (
         NAME SYSNAME,
         ROWS INT,
         RESERVED VARCHAR(20),
         DATA VARCHAR(20),
         INDEX_SIZE VARCHAR(20),
         UNUSED VARCHAR(20)
     )
OPEN TABLE_SPACE
FETCH NEXT FROM TABLE_SPACE INTO @TABLENM
WHILE @@FETCH_STATUS = 0 AND @CNT <= @TOPN
BEGIN
    INSERT INTO #TMPUSAGE 
    EXEC SP_SPACEUSED @TABLENM, 'TRUE'
 IF
    @TOPN <> 0
    SELECT @CNT = @CNT +1
    FETCH NEXT FROM TABLE_SPACE INTO @TABLENM
 END
CLOSE TABLE_SPACE
DEALLOCATE TABLE_SPACE
SELECT 
     *
FROM 
    #TMPUSAGE 
ORDER BY 
    CONVERT(INT,LEFT(RESERVED, LEN(RESERVED)- 3)) DESC

IF (SELECT OBJECT_ID('TEMPDB..#TMPUSAGE') ) IS NOT NULL
DROP TABLE #TMPUSAGE

------解决方案--------------------

SQL code
DECLARE @table_name VARCHAR(500)  
DECLARE @schema_name VARCHAR(500)  
DECLARE @tab1 TABLE( 
        tablename VARCHAR (500) collate database_default 
       ,schemaname VARCHAR(500) collate database_default 
) 

CREATE TABLE #temp_Table ( 
        tablename sysname 
       ,row_count INT 
       ,reserved VARCHAR(50) collate database_default 
       ,data VARCHAR(50) collate database_default 
       ,index_size VARCHAR(50) collate database_default 
       ,unused VARCHAR(50) collate database_default  
) 

INSERT INTO @tab1  
SELECT Table_Name, Table_Schema  
FROM information_schema.tables  
WHERE TABLE_TYPE = 'BASE TABLE' 

DECLARE c1 CURSOR FOR 
SELECT Table_Schema + '.' + Table_Name   
FROM information_schema.tables t1  
WHERE TABLE_TYPE = 'BASE TABLE' 

OPEN c1 
FETCH NEXT FROM c1 INTO @table_name 
WHILE @@FETCH_STATUS = 0  
BEGIN   
        SET @table_name = REPLACE(@table_name, '[','');  
        SET @table_name = REPLACE(@table_name, ']','');  

        -- make sure the object exists before calling sp_spacedused 
        IF EXISTS(SELECT id FROM sysobjects WHERE id = OBJECT_ID(@table_name)) 
        BEGIN 
               INSERT INTO #temp_Table EXEC sp_spaceused @table_name, false; 
        END 
         
        FETCH NEXT FROM c1 INTO @table_name 
END 
CLOSE c1 
DEALLOCATE c1 

SELECT  t1.* 
       ,t2.schemaname  
FROM #temp_Table t1  
INNER JOIN @tab1 t2 ON (t1.tablename = t2.tablename ) 
ORDER BY schemaname,t1.tablename; 

DROP TABLE #temp_Table