QL Server version 7 is an exciting new product. And although it is called “version 7,” so much of it has changed that I consider it a new product rather than an upgrade from version 6.5.
One of the many improvements is how databases are stored. In version 6.5, databases were created on devices. Although for most purposes these devices were simply files, you first had to create the devices before creating a database. In version 7, this artificial abstraction from the file system has been eliminated. Databases are created directly on operating system files.
This architecture allows databases to grow automatically. In version 6.5, you had to allocate space manually. If your DBA didn’t watch carefully, a database could run out of space even though the disk had room. However, files always grow automatically, without intervention, so in version 7, databases can also expand as needed without the DBA doing anything.
Where is the information about the files used for databases stored? In version 6.5, a table in master called sysdevices contained a row for each device used by SQL Server. In version 7, each database contains a table called sysfiles that stores a row for each file used by the database. So how does one obtain a list of all files associated with SQL Server?
Here are the steps:
- Create a cursor on the names of the databases.
- For each row, use dynamic SQL to retrieve the information from the sysfiles of that database and place it in a temporary table.
- Interpret the results and display for output.
Feel free to add to the following code or suggest improvements. If any of you are already using version 7, please let me know what you think of it.
set nocount ongoDECLARE dbcursor CURSORFOR select name from master..sysdatabasesFOR READ ONLY declare @dbname varchar(50), @sqlstmt varchar(200)create table #filelist( dbname varchar(128), filename varchar(128), physname varchar(260), size int, maxsize int, growth int, status int) open dbcursorfetch next from dbcursor into @dbnamewhile @@fetch_status = 0begin set @sqlstmt = 'insert into #filelist (dbname,filename,physname, size,maxsize,growth,status) ' + 'select ' + "'" + @dbname + "'" + ',name,filename,size,maxsize,growth,status from ' + @dbname + '..sysfiles'-- print @sqlstmt exec (@sqlstmt) fetch next from dbcursor into @dbname endclose dbcursordeallocate dbcursorselect dbname,filename,physname, cast(size / 128.0 as decimal(6,2)) as 'Size (MB)',case maxsize when -1 then 'NOLIMIT' else cast(cast(maxsize / 128.0 as decimal(6,2)) as varchar(20))end as 'Max Size (MB)',case when status & 0x100000 > 0 then cast (growth as char(5)) + '%' else cast (cast(growth / 128.0 as decimal(6,2))as varchar(20)) + ' MB'end as 'Growth Factor'from #filelistorder by dbname,filenamegodrop table #filelist 
