Run SQL on each Database

Now there is a non-documented Stored Procedure on SQL Server that will let you run some SQL on each Database. A very handy stored proc, when I went and had a look a the back end of it however I felt it could be a bit better and simpler.

So I made the below, its basically Dynamic SQL and I just pass in the Database and each SQL Command to run.
There are 5 commands in total that you can feed in to run, 3 of these will run on each of the databases, the other two are a pre Command to run first and a post Command to run at the end of the process.
There is a variable '?' which will be replaced with the current database name. This can be used to determine if the current database is to run the command or not.
You can also alter the insert into the @DBs temp table if you wish to only ever use certain Databases if you choose.


Example of use
exec sp_foreachDB
@Command1 = '
if ''?'' not like ''%master%'' begin
select DB_NAME()
end'
,@Command2 = 'PRINT ''?'''
,@Command3 = 'PRINT ''Ash Rocks'''
,@PreCommand = 'PRINT ''Starting App'''
,@PostCommand = 'PRINT ''Finished App'''


Stored Procedure

CREATE PROCEDURE [dbo].[sp_ForEachDB]

@Command1 varchar(7900) = NULL, --Use ? to set the DBName, the If clause can be used to limit to only certain DBs.
@Command2 varchar(7900) = NULL,
@Command3 varchar(7900) = NULL,
@ReplaceChar varchar(20) = '?', --Characters to replace with DBName, default to ?
@PreCommand varchar(8000) = NULL,
@PostCommand varchar(8000) = NULL
AS
BEGIN

SET NOCOUNT ON;
DECLARE @DBs as Table (DBName varchar(100), DBNumber int) --Temp table for DBs to run against
DECLARE @i int --Counter
DECLARE @DBCount int --Number of DBs to run over
DECLARE @DBName as varchar(100) --DB Name to run for instance within loop , used in a USE statement

DECLARE @SQL as varchar(8000) --Total SQL to run

DECLARE @WorkingSQL1 varchar(7900) = ''
DECLARE @WorkingSQL2 varchar(7900) = ''
DECLARE @WorkingSQL3 varchar(7900) = ''

--Find all the Databases on server and put in temp table to run SQL over each
INSERT INTO @DBs
select name as DBName, ROW_NUMBER() OVER(PARTITION BY 1 ORDER BY name) AS DBNumber
from sys.databases

--Select * from @DBs

Select @DBCount = COUNT(*) from @DBs

Select @SQL = @PreCommand
exec(@SQL)

Set @i = 1
WHILE @i <= @DBCount --Start loop over all DB's
BEGIN
Select @DBName = DBname from @DBs where DBNumber = @i
--Run First Command
Select @WorkingSQL1 = replace(@Command1, @ReplaceChar, @DBName)
Select @SQL = 'USE ' + @DBName + ' ' + @WorkingSQL1
--PRINT @SQL
exec(@SQL)
--Run Second Command
Select @WorkingSQL2 = replace(@Command2, @ReplaceChar, @DBName)
Select @SQL = 'USE ' + @DBName + ' ' + @WorkingSQL2
--PRINT @SQL
exec(@SQL)
--Run Third Command
Select @WorkingSQL3 = replace(@Command3, @ReplaceChar, @DBName)
Select @SQL = 'USE ' + @DBName + ' ' + @WorkingSQL3
--PRINT @SQL
exec(@SQL)

Set @i = @i + 1
END

Select @SQL = @PostCommand
exec(@SQL)
END

GO