Apply procedures in multiple databases

Question

I have reach a support line that gave me a T-SQL Script for one DB that I now need to apply many more times.

The original script goes like:

IF EXISTS Procedure 
THEN DROP
GO
CREATE PROCEDURE [dbo].[Procedure]
...
END
GO 
exec Procedure 'With Arguments'

What I'm trying to do goes like:

DECLARE @db_name VARCHAR(50) -- database name 
DECLARE db_cursor CURSOR FOR 
SELECT name FROM sys.databases 
where create_date > DATETIMEFROMPARTS(2017,12,27,0,0,0,0) 
AND create_date < DATETIMEFROMPARTS(2017,12,30,0,0,0,0)
OPEN db_cursor 
FETCH NEXT FROM db_cursor INTO @db_name 
WHILE @@FETCH_STATUS = 0 
BEGIN 
 EXECUTE('USE ' + @db_name) 
 exec Procedure 'With Arguments'
 FETCH NEXT FROM db_cursor INTO @db_name 
END 
CLOSE db_cursor 
DEALLOCATE db_cursor 

But I' m not getting the expected results. I believe that is because I'm not creating the procedure inside each DB. What is the clean way to do the "If Exists" and "Create Procedure" repeatedly?

I also used :

USE master
CREATE PROCEDURE sp_procedure AS ...

and

USE aDB
exec sp_procedure 'With Arguments'

But it says sp_procedure does not exists...

I'm working with windows SQL server 2014

Help appreciated.

EDIT: The end code after reading all the answers and marking as System was:

...
BEGIN 
 SET @CMD = 'exec ' + @db_name + '.dbo.sp_procedure ''With Arguments'';';
 exec sp_executesql @CMD;
 ...

• Michael Green: dba.stackexchange.com/a/63241/36809
• Ezeq: Hi @MichaelGreen; I'm not savvy enough to use your answer to resolve my problem, but after trying @McNets solution it made some sense. I will try the new answers and when I review the supplied procedure in more detail I will try to keep your USE/EXEC Scope in mind. thanks

Answer 1

I believe that is because I'm not creating the procedure inside each DB. What is the clean way to do the "If Exists" and "Create Procedure" repeatedly?

If I understood you correct, you need to execute the same procedure in many databases.

For example, it can be a procedure that makes a script of all the indexes of the specific table, in this case the procedure is always the same but it should be "local" respect to database where the table resides, this way you can use sys.indexes/sys.columns that are "database sensitive".

The simplest way to do this is to make your procedure "system" procedure, i.e. it will be created in master database, it will have sp prefix and marked as system, so that it can be called from any database and will always use system tables "local" to the database from which it was called.

To accomplish this you should create your procedure only one time in master database as you did:

USE master
CREATE PROCEDURE sp_procedure AS ...

And mark this procedure as system one:

exec sys.sp_MS_marksystemobject 'sp_procedure'

Noe when you run your code like this

USE aDB
exec sp_procedure 'With Arguments'

You will not get

sp_procedure does not exists

anymore.

........................................................

If you still prefer to create this procedure many times in every database the simplest way to do it is to save your procedure script in a file including the rows

IF EXISTS Procedure 
THEN DROP
GO

And then launch this script from every database:

sqlcmd -S <ComputerName>\<InstanceName> -d <dbname> -i <MyScript.sql>

(you should add -U MyLogin -P MyPassword if you don't want to use Integrated Security).

You should format this string dynamically in your cursor addind dbname like this:

set @cmd = 'sqlcmd -S <ComputerName>\<InstanceName> -d '+ @db_name + ' -i <MyScript.sql>';
exec master.dbo.xp_cmdshell @cmd

.................................................................

Otherwise, you should put the whole text of your sp (create proc...) in a string variable and execute it in your cursor:

declare @sp varchar(max) = cast(' CREATE PROCEDURE ' as varchar(max)) + '[dbo].[Procedure].....'

declare @sql varchar(max) = 'USE ' + @db_name + @sp
EXECUTE(@sql); 

• Ezeq: thanks; You understand me correctly. After marking as System, the comand: ' SET []CMD = 'exec ' + []db_name + '.dbo.sp_procedure ''With Arguments'' ;'; /// exec sp_executesql []CMD; ' Worked fine. ----------- the others solutions were good to know.

Answer 2

You should build the whole command (not only USE db), and then execute it.

USE [db_test]
GO
IF OBJECT_ID('dbo.sp_Test') IS NOT NULL
 DROP PROCEDURE dbo.sp_Test;
GO
CREATE PROCEDURE dbo.sp_Test
AS
 SELECT 'Got it';
GO
USE [master]
GO
DECLARE @CMD NVARCHAR(MAX);
SET @CMD = 'USE [db_test]; exec dbo.sp_Test;';
exec sp_executesql @CMD;

Or you could add the db to the SP name:

DECLARE @CMD NVARCHAR(MAX);
SET @CMD = 'exec dbTest.dbo.sp_Test;';
exec sp_executesql @CMD;

I've set up a fiddle:

CREATE PROCEDURE dbo.sp_Test
AS
 SELECT 'Got it';
GO

✓

DECLARE @CMD NVARCHAR(MAX);
SET @CMD = 'exec ' + DB_NAME() + '.dbo.sp_Test;';
exec sp_executesql @CMD;
GO

| (No column name) |
| :--------------- |
| Got it |

dbfiddle here

• Ezeq: I tried your solution and in fact it runs for each DB in the cursor, but for some reason it dosen't return the expected results. It may be due to the inner work of the supplied procedure, and maybe do to context as pointed out by @Michael Green
• Ezeq: after mark the procedure as system one, like @sepupic sugest, it worked

Answer 3

To address your specific error and why it happens:

When you run EXECUTE('USE ' + @db_name), that command is run within the scope of the EXECUTE statement. Once the statement concludes, you're returned to the database you were originally connected to.

So, when you run exec Procedure 'With Arguments', that's not running the database named in @db_name, but in the database you originally were connected to when then script started.

This is why the other answers indicate that you need to create a variable that contains your full set of SQL commands to execute together:

DECLARE @stmt NVARCHAR(max);
SET @stmt = N'USE ' + @db_name + N';
exec Procedure ''With Arguments'';'
EXECUTE sp_executesql @stmt;

sepupic's instructions to set up the procedure in the master database, and then mark the procedure as a system procedure should make things simpler and more maintainable, as you only need to create the actual procedure once per SQL Server instance, instead of once per database.

• Ezeq: thanks for the enlightenment, it clarifies the context. For some strange reason the suggested Execute(...) generated an Incorrect syntax error.
• RDFozz: Updated, to match how i'd normally do it; I'd just copied the code from other parts of the post. Put the statement into an nvarchar variable, and excute it with the sp_executesql procedure; that allows parameterization (not needed here, but a good habit to learn).