Get File Info
This script organizes information about the database files on the server. A cursor is used to get the information from each database. The information is reported in Megabytes, including the next allocation from disk. No bells and whistles...and if I can find how dbcc showfilestats works I will add the amount of space used in the file to the script.
DECLARE @DBName nvarchar(20)
DECLARE @SQLString nvarchar (2000)
DECLARE c_db CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
CREATE TABLE #TempForDataFile ([Server Name] nvarchar(40),
[Database Name] nvarchar(20),
[File] nvarchar(128),
[Size (MB)] real,
[MaxSize (MB)] real,
[Next Extent (MB)] real,
[Device Type] varchar (6),
[Growth Type] varchar (12),
[File Id] smallint,
[Group Id] smallint,
[Physical File] nvarchar (260),
[Date Checked] datetime)
OPEN c_db
FETCH NEXT FROM c_db INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQLString = 'SELECT @@SERVERNAME as ''ServerName'', ' +
'''' + @DBName + '''' + ' as ''Database'', ' +
' f.name, ' +
' f.size*8/1024.00 as ''Size (MB)'', ' +
' CASE f.maxsize ' +
' WHEN -1 THEN NULL ' +
' WHEN 0 THEN f.size*8/1024.00 ' +
' ELSE f.maxsize*8/1024.00 ' +
' END as ''Max Size (MB)'', ' +
' CASE ' +
' WHEN (1048576&f.status) = 1048576 THEN (growth/100.00)*(f.size*8/1024.00) ' +
' WHEN f.growth =0 THEN NULL ' +
' ELSE f.growth*8/1024.00 ' +
' END as ''Next Extent (MB)'', ' +
' CASE ' +
' WHEN (64 & f.status) = 64 THEN ''Log'' ' +
' ELSE ''Data'' ' +
' END as ''DeviceType'', ' +
' CASE ' +
' WHEN (1048576&f.status) = 1048576 THEN ''Percentage'' ' +
' ELSE ''Pages'' ' +
' END as ''Growth Type'', ' +
' f.fileid, ' +
' f.groupid, ' +
' filename, ' +
' getdate() ' +
' FROM ' + @DBName + '.dbo.sysfiles f'
INSERT #TempForDataFile
EXECUTE(@Sqlstring)
FETCH NEXT FROM c_db INTO @DBName
END
DEALLOCATE c_db
select * from #tempfordatafile
drop TABLE #TempForDataFile 
