Posted by Erland Sommarskog on 02/07/07 22:35

SQLJunkie (vsinha73@gmail.com) writes:
> I have a small SQL script that rotates through all databases on the
> server and executes a Stored Procedure in each of them. Here are the
> steps:
> 1: The first step is to get name of databases in to a cursor.
> 2: Build dynamic SQL to change database to database in variable - this
> step fails
> 3: Execute SP
> 4: Move to next database.
> Is it not possible to change current database using Dynamic SQL? Can
> someone help me here please?

Yes, but the effect of the USE lasts only for the duration of the of the
dynamic SQL.

> SELECT
> @SQL = 'USE ' + @DatabaseName
>
> EXEC(@SQL)
>
> ----- SQL Statement to be run
> EXEC dbo.SPName

If all you want to do is to run a stored procedure in each database,
this is the easiest way to do:

SELECT @SPname = @DatabaseName + '.dbo.SPName'
EXEC @SPname

You may also be interested in sp_MSforeachdb:

EXEC sp_MSforeachdb 'EXEC ?.dbo.SPNAme'

This procedure is undocumented and unsupported, but it's nevertheless
fairly popular.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]