Check Datafile Space

--查詢DB datafile 資料

DECLARE @DBName varchar(50)
DECLARE @s1 varchar(max)
CREATE TABLE #DBFileList ( [DBName] varchar(50), [FileID] tinyint,
[FileType] char(8), [CurrentSize(MB)] int, [FreeSize(MB)] int, 
[FileName] varchar(50), [path] varchar(200), [Status] char(7)) 
DECLARE dbfile CURSOR FOR 
SELECT name FROM sys.databases
OPEN dbfile FETCH NEXT FROM dbfile INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @s1 =
'USE '+@DBName+'; '+
'INSERT INTO #DBFileList '+
'SELECT '''+@DBName+''' AS [DBName], 
file_id AS [FileID], 
CASE type WHEN 0 THEN ''DataFile'' 
WHEN 1 THEN ''LogFile'' 
ELSE ''Other'' END AS [FileType], 
size/128 AS [CurrentSize(MB)], 
size/128 - CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT)/128 AS [FreeSize(MB)], 
name AS [FileName], physical_name AS [Path], state_desc AS [Status] 
FROM sys.database_files; '
EXEC (@s1)
FETCH NEXT FROM dbfile INTO @DBName
END
CLOSE dbfile
DEALLOCATE dbfile
SELECT * FROM #DBFileList ORDER BY 3, 4 DESC
DROP TABLE #DBFileList
-- 參考 我自己