Friday, December 17, 2010

Display Names and Locations of SQL Database Files and DB Owners

As part of maintaining shared development servers used by many programmers, PMs and other technical personnel, I frequently check that users aren’t placing SQL database files in places where they shouldn’t be.  As I rapidly grew tired of monitoring this using other means, I came up with the script below which displays the names of all of the SQL databases on a SQL Server instance, their data and transaction log files, their owner, and their physical location.   I hope that this will be helpful to anyone who finds themselves in a similar situation.
/*************************************************
Author:     Chicago Computer Consultants [Charles E. Brinkman]
Date:       12.17.10   
Title:      Find Physical Files Associated with SQL DBS
Purpose:    To display displays the names of all of the SQL databases on a SQL Server instance, their data and transaction log files, their owner, and their physical location.
*************************************************/
SELECT
      sys.databases.name AS [Database Name],
      suser_sname(owner_sid) AS [Owner Name],
      sys.master_files.name AS [File Name],
      sys.master_files.physical_name AS Location
FROM sys.databases
      RIGHT JOIN sys.master_files ON sys.databases.database_id = sys.master_files.database_id

/*Update the ORDER BY values below to display the results in a way that will be helpful to you*/

ORDER BY Location, sys.databases.name

No comments:

Post a Comment