计算数据库中各个表的数据量和每行记录所占用空间

来源：互联网发布：淘宝购物车的东西少了编辑：程序博客网时间：2024/05/20 21:45
CREATE TABLE #tablespaceinfo    (      nameinfo VARCHAR(500) ,      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]
转自：http://www.cnblogs.com/lyhabc/p/3828496.html
0 0