You probably already know that your database consists of regular operating system files. These files are created on the disk in a predetermined location and are opened when SQL Server starts and makes the database ready for use. You can expose information about your database files—and thus the database itself—through a system table called sysfiles.

Sysfiles is a read-only table that is considered virtual when SQL Server opens your database for use. You can select from sysfiles to obtain information, but you cannot modify the table in any way. A simple select statement for sysfiles would be similar to:

SELECT * FROM pubs.dbo.sysfiles

This statement returns a good deal of information about your database, such as the size of the db file (in 8 K pages), the filegroup it belongs to, the logical name of the file, or the physical name and path of the file.

You can cursor on this table, assign variables from the table to fulfill an application programming need, or just quickly view the physical layout of your database.

All of this information can prove helpful if you need to quickly gather information about the physical database files.