Introduction
Sometimes we want to run a query or a set of queries across all databases on a SQL server. A common application is to collect usage metrics using database-specific dynamic management views. If the databases share a common structure, then many other reporting, maintenance, and integrity needs could be met by frequently checking their structure, usage, or metadata regularly.
sp_msforeachdb is an undocumented stored procedure that does just that. Because it is undocumented, though, it gets no mention on MSDN, nor does it get much attention from the SQL Server community. While it does the job seemingly well, it has several weaknesses. As a result, I have come to classify it as unreliable and NEVER to be used in any environment where accuracy and predictability are important.
Analysis of sp_msforeachdb
Here is the complete definition of this stored procedure:
ALTER proc [sys].[sp_MSforeachdb]
@command1 nvarchar(2000), @replacechar nchar(1) = N'?', @command2 nvarchar(2000) = null, @command3 nvarchar(2000) = null,
@precommand nvarchar(2000) = null, @postcommand nvarchar(2000) = null
as
set deadlock_priority low
/* This proc returns one or more rows for each accessible db, with each db defaulting to its own result set */ /* @precommand and @postcommand may be used to force a single result set via a temp table. */ /* Preprocessor won't replace within quotes so have to use str(). */ declare @inaccessible nvarchar(12), @invalidlogin nvarchar(12), @dbinaccessible nvarchar(12)
select @inaccessible = ltrim(str(convert(int, 0x03e0), 11))
select @invalidlogin = ltrim(str(convert(int, 0x40000000), 11))
select @dbinaccessible = N'0x80000000' /* SQLDMODbUserProf_InaccessibleDb; the negative number doesn't work in convert() */ if (@precommand is not null)
exec(@precommand)
declare @origdb nvarchar(128)
select @origdb = db_name()
/* If it's a single user db and there's an entry for it in sysprocesses who isn't us, we can't use it. */ /* Create the select */ exec(N'declare hCForEachDatabase cursor global for select name from master.dbo.sysdatabases d ' +
N' where (d.status & ' + @inaccessible + N' = 0)' +
N' and (DATABASEPROPERTYEX(d.name, ''UserAccess'') <> ''SINGLE_USER'' and (has_dbaccess(d.name) = 1))' )
declare @retval int
select @retval = @@error
if (@retval = 0)
exec @retval = sys.sp_MSforeach_worker @command1, @replacechar, @command2, @command3, 1
if (@retval = 0 and @postcommand is not null)
exec(@postcommand)
declare @tempdb nvarchar(258)
SELECT @tempdb = REPLACE(@origdb, N']', N']]')
exec (N'use ' + N'[' + @tempdb + N']')
return @retvalThis SQL definitely doesn’t look friendly, nor is it very well documented internally. The parameters that it takes are as follows:
- @command1: This is the T-SQL you want to execute across all databases.
- @replacechar: This character will be replaced with the database name of whatever database is having the @command1 T-SQL run on it. This is useful if you want to include a USE statement or the name of the database within the T-SQL. This defaults to the ‘?’ character.
- @command2: This is a second T-SQL command that will be executed after @command1.
- @command3: This is a third T-SQL command that will be executed after @command2.
- @precommand: This is T-SQL that will be executed a single time prior to the execution of @command1.
- @postcommand: This is T-SQL that will be executed a single time after the execution of @command3.
Once the parameters and their usage are figured out, the stored procedure definition makes a lot more sense, except for one piece: a call to sys.sp_MSforeach_worker. The guts of the T-SQL execution occur within this stored procedure, whose text I am including here as a reference:
ALTER proc [sys].[sp_MSforeach_worker]
@command1 nvarchar(2000), @replacechar nchar(1) = N'?', @command2 nvarchar(2000) = null, @command3 nvarchar(2000) = null, @worker_type int =1
as
create table #qtemp ( /* Temp command storage */ qnum int NOT NULL,
qchar nvarchar(2000) COLLATE database_default NULL
)
set nocount on
declare @name nvarchar(517), @namelen int, @q1 nvarchar(2000), @q2 nvarchar(2000)
declare @q3 nvarchar(2000), @q4 nvarchar(2000), @q5 nvarchar(2000)
declare @q6 nvarchar(2000), @q7 nvarchar(2000), @q8 nvarchar(2000), @q9 nvarchar(2000), @q10 nvarchar(2000)
declare @cmd nvarchar(2000), @replacecharindex int, @useq tinyint, @usecmd tinyint, @nextcmd nvarchar(2000)
declare @namesave nvarchar(517), @nametmp nvarchar(517), @nametmp2 nvarchar(258)
declare @local_cursor cursor
if @worker_type=1
set @local_cursor = hCForEachDatabase
else
set @local_cursor = hCForEachTable
open @local_cursor
fetch @local_cursor into @name
/* Loop for each database */ while (@@fetch_status >= 0) begin
/* Initialize. */ /* save the original dbname */ select @namesave = @name
select @useq = 1, @usecmd = 1, @cmd = @command1, @namelen = datalength(@name)
while (@cmd is not null) begin /* Generate @q* for exec() */ /*
* Parse each @commandX into a single executable batch.
* Because the expanded form of a @commandX may be > OSQL_MAXCOLLEN_SET, we'll need to allow overflow.
* We also may append @commandX's (signified by '++' as first letters of next @command).
*/ select @replacecharindex = charindex(@replacechar, @cmd)
while (@replacecharindex <> 0) begin
/* 7.0, if name contains ' character, and the name has been single quoted in command, double all of them in dbname */ /* if the name has not been single quoted in command, do not doulbe them */ /* if name contains ] character, and the name has been [] quoted in command, double all of ] in dbname */ select @name = @namesave
select @namelen = datalength(@name)
declare @tempindex int
if (substring(@cmd, @replacecharindex - 1, 1) = N'''') begin
/* if ? is inside of '', we need to double all the ' in name */ select @name = REPLACE(@name, N'''', N'''''')
end else if (substring(@cmd, @replacecharindex - 1, 1) = N'[') begin
/* if ? is inside of [], we need to double all the ] in name */ select @name = REPLACE(@name, N']', N']]')
end else if ((@name LIKE N'%].%]') and (substring(@name, 1, 1) = N'[')) begin
/* ? is NOT inside of [] nor '', and the name is in [owner].[name] format, handle it */ /* !!! work around, when using LIKE to find string pattern, can't use '[', since LIKE operator is treating '[' as a wide char */ select @tempindex = charindex(N'].[', @name)
select @nametmp = substring(@name, 2, @tempindex-2 )
select @nametmp2 = substring(@name, @tempindex+3, len(@name)-@tempindex-3 )
select @nametmp = REPLACE(@nametmp, N']', N']]')
select @nametmp2 = REPLACE(@nametmp2, N']', N']]')
select @name = N'[' + @nametmp + N'].[' + @nametmp2 + ']'
end else if ((@name LIKE N'%]') and (substring(@name, 1, 1) = N'[')) begin
/* ? is NOT inside of [] nor '', and the name is in [name] format, handle it */ /* j.i.c., since we should not fall into this case */ /* !!! work around, when using LIKE to find string pattern, can't use '[', since LIKE operator is treating '[' as a wide char */ select @nametmp = substring(@name, 2, len(@name)-2 )
select @nametmp = REPLACE(@nametmp, N']', N']]')
select @name = N'[' + @nametmp + N']'
end
/* Get the new length */ select @namelen = datalength(@name)
/* start normal process */ if (datalength(@cmd) + @namelen - 1 > 2000) begin
/* Overflow; put preceding stuff into the temp table */ if (@useq > 9) begin
close @local_cursor
if @worker_type=1
deallocate hCForEachDatabase
else
deallocate hCForEachTable
RAISERROR(55555, 16, 1); -- N'sp_MSforeach_worker assert failed: command too long'
return 1
end
if (@replacecharindex < @namelen) begin
/* If this happened close to beginning, make sure expansion has enough room. */ /* In this case no trailing space can occur as the row ends with @name. */ select @nextcmd = substring(@cmd, 1, @replacecharindex)
select @cmd = substring(@cmd, @replacecharindex + 1, 2000)
select @nextcmd = stuff(@nextcmd, @replacecharindex, 1, @name)
select @replacecharindex = charindex(@replacechar, @cmd)
insert #qtemp values (@useq, @nextcmd)
select @useq = @useq + 1
continue
end
/* Move the string down and stuff() in-place. */ /* Because varchar columns trim trailing spaces, we may need to prepend one to the following string. */ /* In this case, the char to be replaced is moved over by one. */ insert #qtemp values (@useq, substring(@cmd, 1, @replacecharindex - 1))
if (substring(@cmd, @replacecharindex - 1, 1) = N' ') begin
select @cmd = N' ' + substring(@cmd, @replacecharindex, 2000)
select @replacecharindex = 2
end else begin
select @cmd = substring(@cmd, @replacecharindex, 2000)
select @replacecharindex = 1
end
select @useq = @useq + 1
end
select @cmd = stuff(@cmd, @replacecharindex, 1, @name)
select @replacecharindex = charindex(@replacechar, @cmd)
end
/* Done replacing for current @cmd. Get the next one and see if it's to be appended. */ select @usecmd = @usecmd + 1
select @nextcmd = case (@usecmd) when 2 then @command2 when 3 then @command3 else null end
if (@nextcmd is not null and substring(@nextcmd, 1, 2) = N'++') begin
insert #qtemp values (@useq, @cmd)
select @cmd = substring(@nextcmd, 3, 2000), @useq = @useq + 1
continue
end
/* Now exec() the generated @q*, and see if we had more commands to exec(). Continue even if errors. */ /* Null them first as the no-result-set case won't. */ select @q1 = null, @q2 = null, @q3 = null, @q4 = null, @q5 = null, @q6 = null, @q7 = null, @q8 = null, @q9 = null, @q10 = null
select @q1 = qchar from #qtemp where qnum = 1
select @q2 = qchar from #qtemp where qnum = 2
select @q3 = qchar from #qtemp where qnum = 3
select @q4 = qchar from #qtemp where qnum = 4
select @q5 = qchar from #qtemp where qnum = 5
select @q6 = qchar from #qtemp where qnum = 6
select @q7 = qchar from #qtemp where qnum = 7
select @q8 = qchar from #qtemp where qnum = 8
select @q9 = qchar from #qtemp where qnum = 9
select @q10 = qchar from #qtemp where qnum = 10
truncate table #qtemp
exec (@q1 + @q2 + @q3 + @q4 + @q5 + @q6 + @q7 + @q8 + @q9 + @q10 + @cmd)
select @cmd = @nextcmd, @useq = 1
end /* while @cmd is not null, generating @q* for exec() */ /* All commands done for this name. Go to next one. */ fetch @local_cursor into @name
end /* while FETCH_SUCCESS */ close @local_cursor
if @worker_type=1
deallocate hCForEachDatabase
else
deallocate hCForEachTable
return 0If we thought this was going to be even the least bit simple, then we were definitely fooled! This additional stored procedure is generic, and built to be used by many system stored procedures, and so it includes additional logic that we don’t need for our purposes.
In order to avoid a complete analysis of this pile of T-SQL, we will focus our attention on a few key concerns that arise from a closer look at both of these stored procedures:
set deadlock_priority low
By setting the deadlock_priority environment variable to low, the T-SQL passed in will always be chosen as the deadlock victim, unless it is deadlocked with another statement with the same priority. This isn’t bad in of itself, but it sets the tone of extreme caution exercised in both of these stored procedures.
exec(N'declare hCForEachDatabase cursor global for select name from master.dbo.sysdatabases d ' +
N' where (d.status & ' + @inaccessible + N' = 0)' +
N' and (DATABASEPROPERTYEX(d.name, ''UserAccess'') <> ''SINGLE_USER'' and (has_dbaccess(d.name) = 1))' )This dynamic SQL is checking to see if a database has a status that indicates that it is inaccessible, that the database is not in single user mode, and that the user executing the SQL can access the database. @inaccessible is a set of bits defined further up in the stored procedure that covers the following options:
- 1 = autoclose; set with ALTER DATABASE.
- 4 = select into/bulkcopy; set with ALTER DATABASE RECOVERY.
- 8 = trunc. log on chkpt; set with ALTER DATABASE RECOVERY.
- 16 = torn page detection, set with ALTER DATABASE.
- 32 = loading.
- 64 = pre recovery.
- 128 = recovering.
- 256 = not recovered.
- 512 = offline; set with ALTER DATABASE.
- 1024 = read only; set with ALTER DATABASE.
- 2048 = dbo use only; set with ALTER DATABASE RESTRICTED_USER.
- 4096 = single user; set with ALTER DATABASE.
- 32768 = emergency mode.
- 4194304 = autoshrink , set with ALTER DATABASE.
- 1073741824 = cleanly shutdown.
0x03e0 converts to 992 in decimal, which equates to the options highlighted in bold above. Additionally, sysdatabases is a deprecated view that is provided by SQL Server for backwards compatibility only. This is an indicator that Microsoft does not update sp_msforeachdb with each new release and it could break in a future version when system views change or this view is removed.
select @retval = @@error
This checks for any errors and skips execution of our SQL if any occur.
The actual SQL execution occurs via a cursor, with quite a bit of string manipulation to properly handle the @replacechar parameter.
Lots of obfuscated security checks, deprecated views, and incomprehensible string manipulation make me nervous enough that I would never, ever use sp_msforeachdb in any production environment for anything that I consider important.
Building a Better Stored Procedure
We can do better, so let’s start from scratch and build a simple, yet effective stored procedure that will reliably run T-SQL commands across databases. In addition, we’ll include options that we would actually want to use!
Overall, Microsoft had the right idea. Create a cursor, iterate through databases, run our T-SQL, and allow for the local use of the database name as needed. Let’s create a simple stored procedure that does just that:
CREATE PROCEDURE dbo.run_query_across_databases
@sql_command VARCHAR(MAX)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @database_name VARCHAR(300) -- Stores database name for use in the cursor
DECLARE @sql_command_to_execute NVARCHAR(MAX) -- Will store the TSQL after the database name has been inserted
-- Stores our final list of databases to iterate through, after filters have been applied
DECLARE @database_names TABLE
(database_name VARCHAR(100))
DECLARE @SQL VARCHAR(MAX) -- Will store TSQL used to determine database list
SET @SQL =
' SELECT
SD.name AS database_name
FROM sys.databases SD
'
-- Prepare database name list
INSERT INTO @database_names
( database_name )
EXEC (@SQL)
DECLARE db_cursor CURSOR FOR SELECT database_name FROM @database_names
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @database_name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql_command_to_execute = REPLACE(@sql_command, '?', @database_name) -- Replace "?" with the database name
EXEC sp_executesql @sql_command_to_execute
FETCH NEXT FROM db_cursor INTO @database_name
END
CLOSE db_cursor;
DEALLOCATE db_cursor;
END
GOThis stored procedure is straightforward compared to sp_msforeachdb, leaving out all of the bells and whistles that Microsoft tried to implement, or that we would want to add in. The single parameter we pass into this stored procedure is the T-SQL we wish to execute across all databases. The database name replacement character is defaulted to ‘?’, and the command is run on all databases with no filter on the database name or type.
A cursor is used to iterate through database names; we could use a while loop instead, but the functionality is basically the same, and the cursor is more convenient. The @sql_command_to_execute parameter is used to store the T-SQL with the ‘?’ replaced with the database name.
Let’s run a test of this stored procedure to see how it works:
CREATE TABLE ##database_files
(
database_name NVARCHAR(128),
fileid INT,
file_type TINYINT,
name NVARCHAR(128),
physical_name NVARCHAR(260)
)
EXEC run_query_across_databases_TEST '
USE [?]
INSERT INTO ##database_files
( database_name,
fileid,
file_type,
name,
physical_name)
SELECT
DB_NAME(),
file_id,
type,
name,
physical_name
FROM sys.database_files'
SELECT * FROM ##database_files
DROP TABLE ##database_filesThis test collects database file information from each database and puts it into a global temp table, returning all data at the end. The USE statement will have the ‘?’ replaced with the database name, thereby switching our context to each database as our stored proc iterates through them. The results are what we expect:
We could similarly run updates, inserts, or other select statements using this new stored proc, bearing in mind that any output from a SELECT statement will spawn a new output set for each database. As a result, inserting that data into a table prior to using it could be a convenient alternative for a large number of databases (and any reporting on them).
The most likely options we would look for in a T-SQL running stored proc would be a ways to easily include, exclude, or otherwise specify a subset of databases to run our SQL against. The first options that come to mind would be a flag to include/not include system databases, and a few parameters for like, not like, and equals. Adding these parameters requires some additional dynamic SQL, but otherwise does not increase the complexity by much:
CREATE PROCEDURE dbo.run_query_across_databases
@sql_command VARCHAR(MAX),
@system_databases BIT = 1,
@database_name_like VARCHAR(100) = NULL,
@database_name_not_like VARCHAR(100) = NULL,
@database_name_equals VARCHAR(100) = NULL
AS
BEGIN
SET NOCOUNT ON;
DECLARE @database_name VARCHAR(300) -- Stores database name for use in the cursor
DECLARE @sql_command_to_execute NVARCHAR(MAX) -- Will store the TSQL after the database name has been inserted
-- Stores our final list of databases to iterate through, after filters have been applied
DECLARE @database_names TABLE
(database_name VARCHAR(100))
DECLARE @SQL VARCHAR(MAX) -- Will store TSQL used to determine database list
SET @SQL =
' SELECT
SD.name AS database_name
FROM sys.databases SD
WHERE 1 = 1
'
IF @system_databases = 0 -- Check if we want to omit system databases
BEGIN
SET @SQL = @SQL + '
AND SD.name NOT IN (''master'', ''model'', ''msdb'', ''tempdb'')
'
END
IF @database_name_like IS NOT NULL -- Check if there is a LIKE filter and apply it if one exists
BEGIN
SET @SQL = @SQL + '
AND SD.name LIKE ''%' + @database_name_like + '%''
'
END
IF @database_name_not_like IS NOT NULL -- Check if there is a NOT LIKE filter and apply it if one exists
BEGIN
SET @SQL = @SQL + '
AND SD.name NOT LIKE ''%' + @database_name_not_like + '%''
'
END
IF @database_name_equals IS NOT NULL -- Check if there is an equals filter and apply it if one exists
BEGIN
SET @SQL = @SQL + '
AND SD.name = ''' + @database_name_equals + '''
'
END
-- Prepare database name list
INSERT INTO @database_names
( database_name )
EXEC (@SQL)
DECLARE db_cursor CURSOR FOR SELECT database_name FROM @database_names
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @database_name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql_command_to_execute = REPLACE(@sql_command, '?', @database_name) -- Replace "?" with the database name
EXEC sp_executesql @sql_command_to_execute
FETCH NEXT FROM db_cursor INTO @database_name
END
CLOSE db_cursor;
DEALLOCATE db_cursor;
END
GO- @system_databases, when set to 0, will explicitly filter out msdb, master, tempdb, and model from our database list.
- @database_name_like will filter out any databases that are not like the string passed in for the parameter (with wildcards before and after).
- @database_name_not_like will filter out any databases that are like the string passed in for the parameter (also with wildcards surrounding it).
- @database_name_equals will filter out any databases that do not have the exact name specified (with no wildcards).
Using these parameters, we can easily pick and choose the specific databases we are looking for. Since the dynamic SQL is additive---ie, there are no ELSE statements in between each IF…THEN, we can mix and match these parameters to our heart’s content. They can also be left NULL, causing them to be ignored. One last note about the SQL above: Each piece of dynamic SQL is strung together with an AND. OR may be used instead of you’d like to be more inclusive, rather than exclusive in your queries (or you can issue multiple commands with a variety of parameters).
If we run a variant on our SQL from earlier, we can verify the results:
CREATE TABLE ##database_files
(
database_name NVARCHAR(128),
fileid INT,
file_type TINYINT,
name NVARCHAR(128),
physical_name NVARCHAR(260)
)
EXEC run_query_across_databases '
USE [?]
INSERT INTO ##database_files
( database_name,
fileid,
file_type,
name,
physical_name)
SELECT
DB_NAME(),
file_id,
type,
name,
physical_name
FROM sys.database_files',
0, 'adventure'
SELECT * FROM ##database_files
DROP TABLE ##database_filesThis returns database file info for AdventureWorks, as it is the only database on my local server with “adventure” in the name:
Lastly, we can add an additional option allowing us to supply a specific list of databases to execute our T-SQL against. This could be useful in scenarios where we want complete control over a unique list of databases. In order to do this, we will create a custom table type that will hold our list of database names:
CREATE TYPE dbo.database_name_list AS TABLE (database_name SYSNAME NOT NULL);
Now that we have created our table type, we can pass it into our stored procedure as a table-valued parameter:
ALTER PROCEDURE dbo.run_query_across_databases
@sql_command VARCHAR(MAX),
@system_databases BIT = 1,
@database_name_like VARCHAR(100) = NULL,
@database_name_not_like VARCHAR(100) = NULL,
@database_name_equals VARCHAR(100) = NULL,
@database_list dbo.database_name_list READONLY
AS
BEGIN
SET NOCOUNT ON;
-- Check if there is a database list to parse
DECLARE @database_list_count INT = (SELECT COUNT(*) FROM @database_list)
DECLARE @database_name VARCHAR(300) -- Stores database name for use in the cursor
DECLARE @sql_command_to_execute NVARCHAR(MAX) -- Will store the TSQL after the database name has been inserted
-- Stores our final list of databases to iterate through, after filters have been applied
DECLARE @database_names TABLE
(database_name VARCHAR(100))
DECLARE @SQL VARCHAR(MAX) -- Will store TSQL used to determine database list
SET @SQL =
' SELECT
SD.name AS database_name
FROM sys.databases SD
WHERE 1 = 1
'
IF @system_databases = 0 -- Check if we want to omit system databases
BEGIN
SET @SQL = @SQL + '
AND SD.name NOT IN (''master'', ''model'', ''msdb'', ''tempdb'')
'
END
IF @database_name_like IS NOT NULL -- Check if there is a LIKE filter and apply it if one exists
BEGIN
SET @SQL = @SQL + '
AND SD.name LIKE ''%' + @database_name_like + '%''
'
END
IF @database_name_not_like IS NOT NULL -- Check if there is a NOT LIKE filter and apply it if one exists
BEGIN
SET @SQL = @SQL + '
AND SD.name NOT LIKE ''%' + @database_name_not_like + '%''
'
END
IF @database_name_equals IS NOT NULL -- Check if there is an equals filter and apply it if one exists
BEGIN
SET @SQL = @SQL + '
AND SD.name = ''' + @database_name_equals + '''
'
END
IF @database_list_count > 0 AND @database_list_count IS NOT NULL
BEGIN
SELECT
DBLIST.database_name
INTO ##database_list
FROM @database_list DBLIST
SET @SQL = @SQL + '
AND SD.name IN (SELECT database_name FROM ##database_list)
'
END
-- Prepare database name list
INSERT INTO @database_names
( database_name )
EXEC (@SQL)
DECLARE db_cursor CURSOR FOR SELECT database_name FROM @database_names
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @database_name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql_command_to_execute = REPLACE(@sql_command, '?', @database_name) -- Replace "?" with the database name
EXEC sp_executesql @sql_command_to_execute
FETCH NEXT FROM db_cursor INTO @database_name
END
CLOSE db_cursor;
DEALLOCATE db_cursor;
IF (SELECT OBJECT_ID('tempdb..##database_list')) IS NOT NULL
BEGIN
DROP TABLE ##database_list
END
END
GOThe additional T-SQL gets a row count from our table-valued parameter. If the row count is non-zero, the database name list is inserted into a global temp table and dynamic SQL added to check the database list against our input. Global temp tables are used so that they will be accessible from our dynamic SQL statement, which otherwise would be out-of-scope with respect to a local temp table, and therefore unable to read it.
One last test of our stored procedure, using a specific list of databases that we insert into a variable of our custom table type:
CREATE TABLE ##database_files
(
database_name NVARCHAR(128),
fileid INT,
file_type TINYINT,
name NVARCHAR(128),
physical_name NVARCHAR(260)
)
DECLARE @my_db_list AS dbo.database_name_list
INSERT INTO @my_db_list
(
database_name
)
VALUES
('master'),
('AdventureWorks')
EXEC run_query_across_databases '
USE [?]
INSERT INTO ##database_files
( database_name,
fileid,
file_type,
name,
physical_name)
SELECT
DB_NAME(),
file_id,
type,
name,
physical_name
FROM sys.database_files',
1, NULL, NULL, NULL, @my_db_list
SELECT * FROM ##database_files
DROP TABLE ##database_filesThe results of this test will provide database file info for the databases in our list, which includes Master and AdventureWorks. The @systemdatabases flag is set to 1, allowing master to be included in our result set:
Did we neglect all of the attention to security and contention that Microsoft implemented? Definitely. If those are features you need, then feel free to add them. In order for a tool like this to be useful, it must contain the features that you need as options only - not as default settings that are beyond your control. I left these out as, for most use-cases, they will not be terribly important, and certainly not for the examples we are illustrating here.
Conclusion
Undocumented features should be used carefully, and only after significant research has been done to ensure that they are not only reliable, but will be supported thoroughly in future versions of SQL Server. The definitions of any system views, stored procedures, functions, and tables can be easily viewed by expanding the appropriate system sections in Management Studio. You can also use sp_help and sp_helptext to return information about system objects.
Sp_msforeachdb was clearly created to fill a distinct need: running T-SQL commands on all databases on a SQL server. While it appears to work at first glance, a closer inspection reveals many bad decisions in its definition: Deprecated features, hard-to-read T-SQL, unreliable security, options you have no control over, and missing features that would be great had they had been included.
Writing your own version of a system object may sound daunting at first, but much of the complexity can be stripped away in favor of a simpler, more reliable, and customized solution that meets your specific needs.
