Did lots of searching, couldn't find the answer.
I am looking to enumerate all databases and their files on a given server. For example:
I know you can use sys.database_files to pull this information database-by-database. But I am looking for a sql query that will generate this information at a server level.
TIA.
In SQL Server 2005, this is a snap.
select *
from sys.master_files
This doesn't suffer from any of the limitations of sys.database_files in that sys.database_files can be wrong if the database was restored and read only.
|||Here you go.
Warning: sp_msforeachdb is undocumented procedure. Use it for single use. Don't stick on this SP.
Code Snippet
Create Table #Databases
(
[Database] nvarchar(1000),
[File Name] nvarchar(1000),
[File Path] nvarchar(1000)
);
Exec sp_msforeachdb 'Insert Into #Databases select ''?'', name,filename from [?]..sysfiles'
select * from #Databases
|||Thanks. Just what I was looking for.|||Thanks for info on sp_msforeachdb. Very helpful.
Appreciate your time.
No comments:
Post a Comment