【SQL Server】統計表記錄數

      說起統計表的記錄數，第一印象就是count(*), 弊端這裡就不多說了，這裡将為大家介紹一些常用的查詢表記錄數的系統視圖。

      首先，介紹系統存儲過程sp_spaceused， 傳回結果包括行數、保留的磁盤空間以及目前資料庫中的表、索引視圖或 Service Broker 隊列所使用的磁盤空間，或由整個資料庫保留和使用的磁盤空間。使用起來很簡單，直接加需要統計的表名即刻。

EXEC sp_spaceused 'Table_Name';
           

除了表的記錄數，以上語句會傳回很多空間相關的資訊（這裡暫不做介紹）。為了隻擷取傳回的記錄數資訊，檢視了sp_spaceused的源碼（喜歡作啊~~），從中抽取了隻用于統計記錄數的相關表/視圖。

方法一 ， 使用系統視圖sys.dm_db_partition_stats查詢記錄數。

SELECT sum(row_count) as ROW_COUNT FROM sys.dm_db_partition_stats WITH (NOLOCK) WHERE index_id < 2 and object_id = OBJECT_ID('Table_Name')
           

方法二 ，使用系統視圖sys.partitions查詢記錄數。

SELECT sum(rows) as ROW_COUNT from sys.partitions WITH (NOLOCK) where index_id < 2 and object_id = OBJECT_ID('Table_Name')
           

方法三 ，使用來自SQL Server 2000的系統視圖sysindexes，不知道在2000版本中表現如何，在後續的SQL Server版本中sysindexes查詢記錄數或者查詢上次更新統計資訊後變化的記錄數，都會偶爾出現不準确的情況，是以不建議使用。

SELECT rowcnt FROM sysindexes WITH (NOLOCK) WHERE indid IN (0,1) and id = OBJECT_ID('Table_Name')
           

      衍生開來，有時候需要統計資料庫中所有的表分别的記錄數，此時繼續使用以上系統視圖。

方法一 ：

SELECT OBJECT_NAME(object_id) as Table_Name, sum(row_count) as ROW_COUNT FROM sys.dm_db_partition_stats WITH (NOLOCK) WHERE index_id < 2 group by object_id order by ROW_COUNT desc
           

方法二 ：

SELECT OBJECT_NAME(object_id) as Table_Name, sum(rows) as ROW_COUNT from sys.partitions WITH (NOLOCK) where index_id < 2 group by object_id order by ROW_COUNT desc
           

方法三：

SELECT OBJECT_name(id) as Table_Name, rowcnt from sysindexes WITH (NOLOCK) where indid in(0,1) order by rowcnt desc
           

      那麼，temp表的記錄數能不能查呢，答案是必須可以啊

SELECT T.name,
       P.rows,
       T.create_date,
       T.modify_date
FROM Tempdb.sys.tables T
JOIN Tempdb.sys.partitions P
  ON T.object_id = P.object_id
WHERE T.name LIKE N'#tmp%'
ORDER BY T.create_date DESC
           

     最後，再給出一個SQL用于統計資料庫中所有表的記錄數，配置設定的總空間，已使用/未使用空間以及資料頁使用的空間大小。通常在遇到空間不足的問題時，我會使用該SQL統計DB中所有表及其空間使用情況，以便做進一步的清理操作，釋放存儲空間。注意，清理資料隻會釋放資料頁使用的空間，并不會直接釋放磁盤空間(已配置設定空間不會減少)，隻有shrink後才會釋放相應的磁盤空間。

SELECT s.Name AS SchemaName,
       t.NAME AS TableName,
       SUM(p.rows)/COUNT(distinct a.type) AS RowCounts,
       CAST(SUM(a.total_pages) as float) * 8 AS TotalSpaceKB, 
       SUM(a.used_pages) * 8 AS UsedSpaceKB, 
       SUM(a.data_pages) * 8 AS DataSpaceKB,
       CAST((SUM(a.total_pages) - SUM(a.used_pages)) as float)  * 8 AS UnusedSpaceKB,
       MIN(FILEGROUP_NAME(a.data_space_id)) as FileGroup
FROM sys.tables t
JOIN sys.schemas s 
  ON s.schema_id = t.schema_id
JOIN sys.indexes i 
  ON t.OBJECT_ID = i.object_id
JOIN sys.partitions p
  ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
JOIN sys.allocation_units a 
  ON p.partition_id = a.container_id
WHERE t.is_ms_shipped = 0 AND i.OBJECT_ID > 255 AND i.index_id < 2 
GROUP BY t.Name, s.Name
ORDER BY TotalSpaceKB desc