查询表的记录数和空间

   
   =====================方法1====================
   DECLARE @t TABLE
    (
      name VARCHAR(50) ,
      rows INT ,
      reserved VARCHAR(50) ,
      data VARCHAR(50) ,
      index_size VARCHAR(50) ,
      unused VARCHAR(50)
    )
   
   DECLARE @name VARCHAR(50)
   DECLARE tableCursor CURSOR LOCAL
   FOR
    SELECT '[' + name + ']'
    FROM sys.tables
    WHERE type = 'u'
   
   OPEN tableCursor
   FETCH NEXT FROM tableCursor INTO @name
   
   WHILE @@fetch_status = 0
    BEGIN
        INSERT INTO @t
                EXEC sp_spaceused @name
        FETCH NEXT FROM tableCursor INTO @name
    END
   
   
   SELECT *
   FROM @t ORDER BY rows DESC 




===========================方法二========================


DECLARE @tablespaceinfo TABLE (
    [name] SYSNAME,
    [rows] BIGINT,
    [reserved] VARCHAR(100),
    [data] VARCHAR(100),
    [index_size] VARCHAR(100),
    [unused] VARCHAR(100)
)

INSERT INTO @tablespaceinfo EXEC sp_MSforeachtable @command1="sp_spaceused '?'"

SELECT * FROM @tablespaceinfo
ORDER BY rows DESC
   -- ORDER BY Cast(Replace(reserved,'KB','') AS INT) DESC