Ever faced a sitaution where you had to find out all the database files (Data and Log) of a SQL Instance?
I came across this situaion many times, especially when I am working with a new SQL Instance which was taken over by my team OR when the previous admin of that box has left the company and its now my turn to maintain / administer that instance.
In order to list the files, we just need to query the system table sysaltfiles. The picture below is a screen show that I ran earlier today on one of my SQL Instances.
SELECT * FROM SYSALTFILES
This query can be further tweaked to get only the data files or only the log files by adding where clause and filter by griupid column. Groupid = 1 indicates it is a data file and groupid = 0 indicates it is a log file.
Do you like this site? Like our FB page @ Facebook.com\LearnSQLWithBru so that, you know when there is a new blog post.