Wednesday, September 30, 2009

Quick list of Database Filenames for all Databases on an Instance

Brief Description: Script to get a clean list of all database files on an instance without having to manually change databases.

Problem/Symptom: The default tsql to get all filenames for a database requires you be in that database. The information in sysdatabases only lists the first datafile.

I was trying to get a clean list of all datafiles on our sql servers (I'll post a wrapper for dropping it into vbscript to query a list of remote databases later). Selecting the filename from sysfiles only gets the list for the current database, requiring you know all the database names and iterate through. The handy sp_msforeachdb outputs as a single resultset for each database, which is fine, just cumbersome to clean up if you need a list.

It's only one solution and as always, corrections, alternate methods are appreciated. I got the info I wanted in the format I wanted by inserting it into a table variable as below.

declare @dbtable table(
sName nvarchar(75),
sdbname nvarchar(256),
sfilepath nvarchar(256));
insert into @dbtable (sName, sdbname, sfilepath) exec sp_msforeachdb 'use [?]; select @@Servername, name, filename from sysfiles';
select sName, sdbname, sfilepath from @dbtable