SELECTo.name, i. ROWSFROM sysobjects o, sysindexes iWHERE o.id = i.id  AND o.Xtype = ‘U‘ AND i.indid < 2ORDER BY o.name;

--SELECT * FROM Employees AS e;CREATE TABLE #tablespaceinfo    (      nameinfo VARCHAR(50) ,      rowsinfo BIGINT ,      reserved VARCHAR(20) ,      datainfo VARCHAR(20) ,      index_size VARCHAR(20) ,      unused VARCHAR(20)    )   DECLARE @tablename VARCHAR(255);   DECLARE Info_cursor CURSORFOR    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   --创建临时表CREATE TABLE [#tmptb]    (      TableName VARCHAR(50) ,      DataInfo BIGINT ,      RowsInfo BIGINT ,      Spaceperrow  AS ( CASE RowsInfo                         WHEN 0 THEN 0                         ELSE CAST(DataInfo AS decimal(18,2))/CAST(RowsInfo AS decimal(18,2))                       END ) PERSISTED    )--插入数据到临时表INSERT  INTO [#tmptb]        ( [TableName] ,          [DataInfo] ,          [RowsInfo]        )        SELECT  [nameinfo] ,                CAST(REPLACE([datainfo], ‘KB‘, ‘‘) AS BIGINT) AS ‘datainfo‘ ,                [rowsinfo]        FROM    #tablespaceinfo        ORDER BY CAST(REPLACE(reserved, ‘KB‘, ‘‘) AS INT) DESC  --汇总记录SELECT  [tbspinfo].* ,        [tmptb].[Spaceperrow] AS ‘每行记录大概占用空间（KB）‘FROM    [#tablespaceinfo] AS tbspinfo ,        [#tmptb] AS tmptbWHERE   [tbspinfo].[nameinfo] = [tmptb].[TableName]ORDER BY CAST(REPLACE([tbspinfo].[reserved], ‘KB‘, ‘‘) AS INT) DESC  DROP TABLE [#tablespaceinfo]DROP TABLE [#tmptb]