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
