查看数据库表的数据量和SIZE大小的脚本修正

FOR

    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 

修正后的脚本如下所示

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 CURSOR

FOR

    SELECT  ‘[‘ + s.[name] +‘]‘ +‘.‘ + ‘[‘ + t.[name] + ‘]‘

    FROM    sys.tables t

    INNER JOIN sys.schemas s ON t.schema_id = s.schema_id 

    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 tmptb

WHERE   [tbspinfo].[nameinfo] = [tmptb].[TableName]

ORDER BY CAST(REPLACE([tbspinfo].[reserved], ‘KB‘, ‘‘) AS INT) DESC  

DROP TABLE [#tablespaceinfo]

DROP TABLE [#tmptb]

查看数据库表的数据量和SIZE大小的脚本修正

标签：