Posted by SQLJunkie on 02/07/07 15:02

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?

Here is the code I am using:
----- EXECUTE SQL Statement on all Databases

DECLARE
@DatabaseName varchar(100)
, @SQL varchar(500)

DECLARE DBNameCursor CURSOR FOR
SELECT
[Name] AS DatabaseName
FROM
master.dbo.sysdatabases
ORDER BY
DatabaseName

OPEN DBNameCursor

FETCH NEXT FROM DBNameCursor
INTO @DatabaseName

WHILE @@FETCH_STATUS = 0
BEGIN
----- Change to Database
SELECT
@SQL = 'USE ' + @DatabaseName

EXEC(@SQL)

----- SQL Statement to be run
EXEC dbo.SPName

FETCH NEXT FROM DBNameCursor
INTO @DatabaseName
END

CLOSE DBNameCursor
DEALLOCATE DBNameCursor


----------------------
Thanks in advance!
Vishal Sinha

• Next in forum: Re: NULLS and ''
• Prev in forum: Re: Stored procedure and SQL Job Agent Task
• Thread view: Dynamic SQL does not work!

[Reply to this message]