Creating a stored proc in every database

So, the other day, I needed to create a stored procedure in every database. This is what I came up with.


First, we need to make a test set of simple, empty databases:

use master;
go

create database CreateSPTest1;
create database CreateSPTest2;
create database CreateSPTest3;
create database CreateSPTest4;
create database CreateSPTest5;
go

Now, here was my first pass at writing a simple loop to get an object created in the target databases.

declare @currentDB sysname;
declare @sqlToRun nvarchar(max);
declare LoopDBs cursor for
select [name] from sys.databases where name like '%SPTest%';
open LoopDBs;
fetch next from LoopDBs into @currentDB;
while @@FETCH_STATUS = 0
begin
set @sqlToRun = '';
set @sqlToRun = 'USE ' + @currentDB + '; create table t1(c1 int);';
--print @sqlToRun;
exec sp_executesql @sqltorun;
fetch next from LoopDBs into @currentDB;
end
close LoopDBs;
deallocate LoopDBs;
GO

Next, I wanted to extend my loop, above, to create an uber-simple stored procedure in every database. Here's where I hit my first roadblock. No matter what I seemed to, I received an error that looked like this:

Msg 111, Level 15, State 1, Procedure usp1, Line 1 [Batch Start Line 36]
'CREATE/ALTER PROCEDURE' must be the first statement in a query batch.

For what it's worth, here's the loop I was using. As you'll see, the only difference between this and the snippet above is the set @sqlToRun variable.

declare @currentDB sysname;
declare @sqlToRun nvarchar(max);
declare LoopDBs cursor for
select [name] from sys.databases where name like '%SPTest%';
open LoopDBs;
fetch next from LoopDBs into @currentDB;
while @@FETCH_STATUS = 0
begin
 set @sqlToRun = '';
 set @sqlToRun = 'USE ' + @currentDB + '; create proc usp1 as select getdate();';
 --print @sqlToRun;
 exec sp_executesql @sqltorun;
 fetch next from LoopDBs into @currentDB;
end
close LoopDBs;
deallocate LoopDBs;
GO

Lastly, this is what I came up with after a quick Google-Fu. In short, I found that sp_executesql calls can be nested, and, if the first call to sp_executesql contains the USE statement, then the second will operate against the proper database.

declare @currentDB sysname;
declare @sqlToRun nvarchar(max);
declare @sqlForInside nvarchar(max);
declare @params nvarchar(4000);
set @params = '@sqlToRunInside nvarchar(max)';
declare LoopDBs cursor for
select [name] from sys.databases where name like '%SPTest%';
open LoopDBs;
fetch next from LoopDBs into @currentDB;
while @@FETCH_STATUS = 0
begin
 set @sqlForInside = 'create proc usp1 as select getdate();';
 --print @sqlToRun;
 set @sqlToRun = '';
 set @sqlToRun = 'USE ' + @currentDB + '; exec sp_executesql @sqlToRunInside';
 exec sp_executesql @sqltorun, @params, @sqlToRunInside = @sqlForInside;
 fetch next from LoopDBs into @currentDB;
end
close LoopDBs;
deallocate LoopDBs;
GO