SQLSserver数据文件碎片查看

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26

CREATE TABLE #FileSize (dbName NVARCHAR(128),     FileName NVARCHAR(128),     type_desc NVARCHAR(128),     CurrentSizeMB DECIMAL(10,2),     FreeSpaceMB DECIMAL(10,2) );       INSERT INTO #FileSize(dbName, FileName, type_desc, CurrentSizeMB, FreeSpaceMB) exec sp_msforeachdb 'use [?];  SELECT DB_NAME() AS DbName,         name AS FileName,         type_desc,         size/128.0 AS CurrentSizeMB,          size/128.0 - CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT)/128.0 AS FreeSpaceMB FROM sys.database_files WHERE type IN (0,1);';       SELECT * FROM #FileSize WHERE dbName NOT IN ('distribution', 'master', 'model', 'msdb') -- AND FreeSpaceMB > 60 order by 5 desc;       DROP TABLE #FileSize;