CREATE TABLE TABLESPACEINFO --创建结果存储表
(NAMEINFO   VARCHAR(50),
 ROWSINFO   INT,
 RESERVED   VARCHAR(20),
 DATAINFO   VARCHAR(20),
 INDEX_SIZE VARCHAR(20),
 UNUSED     VARCHAR(20) )
 
DECLARE MYCURSOR CURSOR FOR
SELECT NAME FROM SYSOBJECTS WHERE XTYPE='U'
OPEN MYCURSOR
DECLARE @NAME  VARCHAR(100)
FETCH NEXT FROM MYCURSOR INTO @NAME
WHILE(@@FETCH_STATUS =0)
BEGIN
INSERT INTO TABLESPACEINFO EXEC SP_SPACEUSED @NAME 
FETCH NEXT FROM MYCURSOR INTO @NAME
END
CLOSE MYCURSOR
DEALLOCATE MYCURSOR
 

SELECT  NAMEINFO    AS 表名,
        ROWSINFO    AS 记录数,
        RESERVED    AS 表保留的空间,
        DATAINFO    AS 表数据占用空间,
        INDEX_SIZE  AS 表索引占用空间,
        UNUSED      AS 未使用空间
FROM TABLESPACEINFO
ORDER BY CAST(LEFT(LTRIM(RTRIM(RESERVED)) , LEN(LTRIM(RTRIM(RESERVED)))-2) AS INT) DESC