【Sql语句】查询数据库表的空间大小，索引大小的语句

--查询sqlserver数据库，表占用数据大小
IF EXISTS ( SELECT  1
            FROM    tempdb..sysobjects
            WHERE   id = OBJECT_ID('tempdb..#tabName')
                    AND xtype = 'u' )
    DROP TABLE #tabName;
GO
CREATE TABLE #tabName
    (
      tabname VARCHAR(100) ,
      rowsNum VARCHAR(100) ,
      reserved VARCHAR(100) ,
      data VARCHAR(100) ,
      index_size VARCHAR(100) ,
      unused_size 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 #tabName
                EXEC sp_spaceused @name;
    --print @name
 
        FETCH NEXT FROM cur INTO @name;
    END;
CLOSE cur;
DEALLOCATE cur;
-- 已经经过优化
SELECT  tabname AS '表名' ,
        rowsNum AS '表数据行数' ,
        reserved AS '保留大小' ,
        CONVERT(INT, SUBSTRING(data, 0, LEN(data) - 2)) size ,
        data AS '数据大小' ,
        index_size AS '索引大小' ,
        unused_size AS '未使用大小'
FROM    #tabName
ORDER BY size DESC;