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

Rate

Share

Share

Rate