当前位置：首页 > news >正文

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

news 来源：原创 2024/5/19 15:58:06

在使用桦仔的分享一个SQLSERVER脚本（计算数据库中各个表的数据量和每行记录所占用空间）的脚本时，遇到下面一些错误

这个是因为这些表的Schema是Maint，而不是默认的dbo，造成下面这段SQL在执行EXEC sp_spaceused @tablename时出现

Msg 15009, Level 16, State 1, Procedure sp_spaceused, Line 75

The object 'xxxx' does not exist in database 'YourSQLDba' or is invalid for this operation.

   DECLARE Info_cursor CURSOR
   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]
    
  