Join Us!

Spaces are allowed; punctuation is not allowed except for periods, hyphens, and underscores.
A valid e-mail address. All e-mails from the system will be sent to this address. The e-mail address is not made public and will only be used if you wish to receive a new password or wish to receive certain news or notifications by e-mail.
CAPTCHA
This question is for testing whether you are a human visitor and to prevent automated spam submissions.
Image CAPTCHA
Enter the characters shown in the image.

Login

Enter your Project Envision username.
Enter the password that accompanies your username.
Request new password

How to list all database files in SQL Server

When preparing the Sql Server Migrations or performing audits is to list all database files on the server. One way to do it is to go to each database and query sysfiles. However this approach is not too handy where we have many database on a server.

There is another easier and more handy way to get a list of all data files and transaction log on a server.

On Microsoft Sql Server 2000, we can use following script:

select d.name, f.name, f.filename 
from sysaltfiles f
inner join sysdatabases d
on (f.dbid = d.dbid)
order by 1,2

On Microsoft Sql Server 2005 / 2008, we should use the view sys.master_files instead of system table sysaltfiles, because usage of sysaltfiles has been deprecated since version 2005.

select d.name, m.name, m.physical_name
from sys.master_files m
inner join sys.databases d
on (m.database_id = d.database_id)
order by 1, 2

Categories: