首页 > 代码库 > 计算数据库中各个表的数据量和每行记录所占用空间 转

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

 

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]

转自华仔,个人使用

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