[MSSQL]如何找出資料庫中TABLE的資料筆數和所使用空間等資訊

[tim]

在 MSSQL 中, 每個資料庫 (db) 會有一個 sysobjects 的系統資料表,
其中記錄的就是該資料庫的各物件的資訊, 我們可以利用 xType= 'U'
來找出是 User Table 的 Table Name, 再利用 sp_spaceused 的 stored procedure
來找出所有的 table 細節資料, 由www.databasejournal.com 這篇文章中所以用的方式, 轉貼如下:
如此一來就可以取得所有 table 的佔用空間及資料量

代碼: 選擇全部

     
    SET NOCOUNT ON 
     
    CREATE TABLE #TBLSize 
     (Tblname varchar(80),  
     TblRows int, 
     TblReserved varchar(80), 
     TblData varchar(80), 
     TblIndex_Size varchar(80), 
     TblUnused varchar(80)) 
     
    DECLARE @DBname varchar(80)  
    DECLARE @tablename varchar(80)  
     
    SELECT @DBname = DB_NAME(DB_ID()) 
    PRINT 'User Table size Report for (Server / Database):   ' + @@ServerName + ' / ' + @DBName 
    PRINT '' 
    PRINT 'By Size Descending' 
    DECLARE TblName_cursor CURSOR FOR  
    SELECT NAME  
    FROM sysobjects 
    WHERE xType = 'U' 
     
    OPEN TblName_cursor 
     
    FETCH NEXT FROM TblName_cursor  
    INTO @tablename 
     
    WHILE @@FETCH_STATUS = 0 
    BEGIN 
       INSERT INTO #tblSize(Tblname, TblRows, TblReserved, TblData, TblIndex_Size, TblUnused) 
       EXEC Sp_SpaceUsed @tablename 
           
       -- Get the next author. 
       FETCH NEXT FROM TblName_cursor  
       INTO @tablename 
    END 
     
    CLOSE TblName_cursor 
    DEALLOCATE TblName_cursor 
     
    SELECT  CAST(Tblname as Varchar(30)) 'Table', 
    CAST(TblRows as Varchar(14)) 'Row Count', 
    CAST(LEFT(TblReserved, CHARINDEX(' KB', TblReserved)) as int) 'Total Space (KB)', 
            CAST(TblData as Varchar(14)) 'Data Space', 
    CAST(TblIndex_Size  as Varchar(14)) 'Index Space', 
            CAST(TblUnused as Varchar(14)) 'Unused Space' 
    FROM #tblSize 
    Order by 'Total Space (KB)' Desc 
     
    PRINT '' 
    PRINT 'By Table Name Alphabetical' 
     
     
    SELECT  CAST(Tblname as Varchar(30)) 'Table', 
    CAST(TblRows as Varchar(14)) 'Row Count', 
    CAST(LEFT(TblReserved, CHARINDEX(' KB', TblReserved)) as int) 'Total Space (KB)', 
            CAST(TblData as Varchar(14)) 'Data Space', 
    CAST(TblIndex_Size  as Varchar(14)) 'Index Space', 
            CAST(TblUnused as Varchar(14)) 'Unused Space' 
    FROM #tblSize 
    Order by 'Table' 
     
    DROP TABLE #TblSize