[SQL SERVER] 查詢資料表(TABLE)的修改日期、使用空間大小、未使用空間、筆數

2015-11-03

3389
0

摘要:[SQL SERVER] 查詢資料表(TABLE)的修改日期、使用空間大小、未使用空間、筆數

SELECT   
    t.NAME AS 資料表名稱,  
    p.rows AS 筆數,  
    SUM(a.total_pages) * 8 /1024 AS 資料表空間MB,   
    SUM(a.used_pages) * 8 /1024 AS 已使用空間MB,   
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8  /1024 AS 未使用空間MB  
	,o.modify_date 修改日期
FROM sys.tables t INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id  
INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id  
INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id  
INNER JOIN sys.all_objects o ON o.OBJECT_ID = i.OBJECT_ID
WHERE   
    t.NAME NOT LIKE 'dt%' AND t.is_ms_shipped = 0 AND i.OBJECT_ID > 255   
GROUP BY   
    t.Name, p.Rows ,o.modify_date 
ORDER BY   
    已使用空間MB desc

平常我都是直接用物件檔案總管來看的，不過檔案總管並無修改日期

需要到sys.all_objects上查詢

回首頁