|
|
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
Navigation:
[Reply to this message]
|