sp_Msforeachdb Example : List All Database Files using sp_Msforeachdb Undocumented Stored Procedure
In MS SQL Server versions, you can list database files declared in a specific sql databae by executing the below select query on sysfiles system view on the related database.
select * from sysfiles
The select query will return one row for each file in the current SQL Server database.
But if you want to list all of the files in all databases within the current MS SQL Server instance, you should run the above sysfiles query on every database.
But you will get a result set for each select, that means you can not aggregate the solutions in one select.
sysfiles system view is selecting approtiate rows from the sys.sysdbfiles internal system base-table.
Unfortunately system base-tables like sys.sysdbfiles are not for customer use.
System base tables are defined only for use of SQL Server Database Engine.
If you run a SELECT query on sys.sysdbfiles as follows:
select * from sys.sysdbfiles
All you will get is the following error message :
Msg 208, Level 16, State 1, Line 1
Invalid object name 'sys.sysdbfiles'.
Instead of working on system views, etc. I will develop a t-sql code which uses sp_Msforeachdb undocumented stored procedure.
First create a temporary sql table to store info data about data files in sql databases.
create table systemfiles (
fileid smallint,
groupid smallint,
size bigint,
maxsize bigint,
growth float,
status int,
perf int,
name sysname,
filename sysname
);
Now let's execute the following sp_Msforeachdb command.
EXEC sp_Msforeachdb "use [?];insert SamplesDB.dbo.systemfiles select * from sysfiles"
Here is an other sp_Msforeachdb example. In this sp_Msforeachdb sample, the t-sql code sample will find the owner database name whose data files has a name like "kodyaz".
EXEC sp_Msforeachdb "use [?];insert SamplesDB.dbo.systemfiles(fname) select '[?]' from sysfiles where filename like '%kodyaz%'"
More Tutorials on T-SQL sp_MSForEachTable Examples
sp_MSForEachTable Example T-SQL Code to Count all Rows in all Tables in MS SQL Server Database
sp_Msforeachdb Example : List All Database Files using sp_Msforeachdb Undocumented Stored Procedure
Create Same Stored Procedure on All Databases using sp_MSForEachDB T-SQL Example
MS SQL Server Execute Undocumented Stored Procedures sp_MSForEachDB and sp_MSForEachTable with Example T-SQL Codes
Listing All MS SQL Server Databases Using T-SQL
SQL Server Last Database Access using Last Batch Date of sysprocesses or using SQL Server Audit Files and Database Audit Specifications