How to run a stored procedure in several databases

Question

In a single SQL Server instance, I have the following databases:

• Headquarters
• Branch001
• Branch002
• Branch003

...

• Branch*nnn*

In each Branch DB is a procedure called usp_ComputeDailySales that does some computation and writes daily sales figures to the Headquarters DB. How do I write a single stored procedure in the Headquarters DB to execute usp_ComputeDailySales in all Branch DBs?

I could write something like the code below but I think this is not the way to go.

USE Branch001
EXEC usp_ComputeDailySales

USE Branch002
EXEC usp_ComputeDailySales

...

I wanted some way to execute the procedure for all Branch DBs all at the same time.

Answer 1

This is not dynamic SQL, but relies on something like deferred name resolution.

EXECUTE, see @module_name_var parameter

DECLARE @sql varchar(300)
SELECT
    [name] + '..usp_ComputeDailySales' AS Cmd
INTO #List
FROM
    sys.databases d WHERE d.[name] LIKE 'branch%'
    OR --Thanks Mitch
    d.[name] = 'Headquarters'

WHILE EXISTS (SELECT * FROM #List)
BEGIN
    SELECT TOP 1 @SQL = Cmd FROM #List
    EXEC @SQL
    DELETE #List WHERE Cmd = @SQL
END

Edit, after KM's comment

Yes, there is a difference but I'll clarify:

EXEC (@SQL) means @SQL could be 'SELECT * FROM foo' OR 'DELETE User WHERE 1=1' ;.)

EXEC @SQL means @SQL is an object name. So I should have used @Obj

So, you could do this:

DECLARE @Obj varchar(200)
SELECT @Obj = 'dbo.uspCustomHook'
IF OBJECT_ID(@Obj) IS NOT NULL
    EXEC @Obj @p1 = 1, @p2 = 'bob', ...

It avoids deferred name resolution errors. When the batch/proc is compiled, there is no error if dbo.uspCustomHook does not exist.

I've not used the technique since SQL 2000, and I don't know if SQL 2005 (statement level recompilation) is clever enough to work out if @Obj should be be run because it does not exist so it does not try to compile the EXEC statement...

It's also useful for cross database, same instance queries. You can configure prd-prod, dev-dev etc without relying on linked servers or hard coded databases names.

Answer 2

you could use an undocumented stored procedure called sp_MSforeachdb

Answer 3

Just address them with their fully qualified names like :

<Database Name>.<owner>.<table>

and replace the values.

Example : CustomerDatabase.dbo.Users

And in your case it will look like this :

exec Branch001.dbo.usp_ComputeDailySales
exec Branch002.dbo.usp_ComputeDailySales
exec Branch003.dbo.usp_ComputeDailySales

This assumes one user to have permissions for all those databases.

Answer 4

You can do this fairly readily with sp_msForEachDB. The first sample here will run the proc on every database that is named LIKE 'Branch%':

EXECUTE sp_msForEachDB '
IF ''?'' like  ''Branch%''
 BEGIN
    USE ?
    EXECUTE usp_ComputeDailySales
 END
'

And this one will run it on every database (regardless of name) containing a stored procedure by that name:

EXECUTE sp_msForEachDB '
USE ?
IF object_id(''usp_ComputeDailySales'') is not null
    EXECUTE usp_ComputeDailySales
'

Poke around and fuss with these--they work, but they take a bit of getting used to.

Addenda: While this is an "undocumented feature", it's been one since SQL Server 7.0 and possibly earlier. There are any number of articles and samples out there using it, and probably any number of Enterprise systems relying on it, some of which I have built. I've truly never had any problems with it (once I've gotten the ?s and the double 's correct, that is.)