Here’s a quick T-SQL tip on getting the paths for all database files in a SQL Server instance.
The T-SQL Tip
You can get the list of paths for all database files (both data and log files) using the undocumented SQL Server stored procedure sp_MSforeachdb. You can use the below T-SQL statement:
exec sp_MSforeachdb @command1="USE [?]; SELECT CAST(DB_NAME() AS VARCHAR(50)) AS DBName, CAST(physical_name AS VARCHAR(150)) as Files FROM sys.database_files";
Related T-SQL Tips on SQLNetHub
- Executing T-SQL Statements Against All Databases
- Listing all Tables of a Linked Server’s Database
- Inserting Leading Characters to a String
- Retrieving Security-Related Info for SQL Server Logins
- Getting all the Records from all Tables in all User Databases
Reference: The SQL Server and .NET Hub (http://www.sqlnethub.com)
What are your views on the subject? Have something to share? Feel free to leave your comment!
Did you find this article useful and interesting? Find hundreds of useful SQL Server administration articles in Artemakis’s eBook: “Administering SQL Server (Second Edition)“.
Check our other related SQL Server Administration articles.
Subscribe to our newsletter and stay up to date!
Check out our latest software releases!
Rate this article:
Reference: SQLNetHub.com (https://www.sqlnethub.com)
Artemakis Artemiou is a Senior SQL Server Architect, Author, and a 9 Times Microsoft Data Platform MVP (2009-2018). He has over 15 years of experience in the IT industry in various roles. Artemakis is the founder of SQLNetHub and TechHowTos.com. Artemakis is the creator of the well-known software tools Snippets Generator and DBA Security Advisor. Also, he is the author of many eBooks on SQL Server. Artemakis currently serves as the President of the Cyprus .NET User Group (CDNUG) and the International .NET Association Country Leader for Cyprus (INETA).