Solved

select * from sys.database_files

Posted on 2013-01-16
2
692 Views
Last Modified: 2013-01-16
I have a query which allows me to see each database's physical files. Is there a similar query to see physical locations of all databases on a SQL server?

Thank you!
JohnD
0
Comment
Question by:johndarby
2 Comments
 
LVL 22

Accepted Solution

by:
Steve Wales earned 500 total points
ID: 38784988
Well, the files (usually, if held to accepted and recommended naming conventions a minimum of a .MDF and a .LDF file) are what makes up the physical location of the databases.

So what you're seeing in sys.database_files is it, per database.

To see all of them:

exec msdb..sp_msforeachdb 'use [?]; select * from [?].sys.database_files'

Open in new window


Of course, you could replace 'select *' with a selected list of the columns you want to see.

Oh, and here's the entry from the documentation on this view which is a per database view:

http://msdn.microsoft.com/en-us/library/ms174397.aspx
0
 
LVL 1

Author Closing Comment

by:johndarby
ID: 38785011
That is awesome; thank you!
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now