Technical Article

Display Names and Locations of SQL Database Files and DB Owners

,

As part of maintaining shared development servers, I frequently check that users aren’t placing SQL database files in places where they shouldn’t be (restricting this through the file system isn't practical since this is a sandbox).   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.

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

Rate

4.2 (5)

Share

Share

Rate

4.2 (5)