USE @dbname (Use statement with variable)

[AAA]

Hi

I need to run a stored procedure on each database in my SQL server. I want to have a loop to go through each db.

Is there a way I can run 'Use @dbname', I tried Execute and sp_executesql but it didn't work.

I want to execute the SP withing each db.

Thanks

[Karl]

Hi,

there are several ways you can do this. By far the easiest way is to use the system proc sp_MSforeachdb as follows.

sp_MsForeachdb &#39;EXEC <dbname>.dbo.<procname>&#39;

this will execute the proc in every database (including master,model,msdb and tempdb) and you don&#39;t even need to code for the loop.

If you do want to code the loop then you can use the EXEC statement.

DECLARE @DBName varchar(40)

--loop code goes here somewhere

EXEC(&#39;USE &#39; + @DBName + &#39; EXEC <dbname>.dbo.<procname>&#39

Hope that helps,

Karl




------------
AAA at 6/12/2002 4:27:12 PM


Hi

I need to run a stored procedure on each database in my SQL server. I want to have a loop to go through each db.

Is there a way I can run &#39;Use @dbname&#39;, I tried Execute and sp_executesql but it didn&#39;t work.

I want to execute the SP withing each db.

Thanks

[David]

I have a similar situation which is not to exec a proc, but to update existing procs and triggers for each db in the server. dynamic exec &#39;Use...&#39;
did not do much for me since the updProc.sql has > 30,000 chars and with many &#39;go&#39; keywords and /* */ marks.
Any idea to loop to implement changes on one server, and eventually on many servers?
thanks
David

------------
Karl at 6/13/2002 4:12:54 AM

Hi,

there are several ways you can do this. By far the easiest way is to use the system proc sp_MSforeachdb as follows.

sp_MsForeachdb &#39;EXEC <dbname>.dbo.<procname>&#39;

this will execute the proc in every database (including master,model,msdb and tempdb) and you don&#39;t even need to code for the loop.

If you do want to code the loop then you can use the EXEC statement.

DECLARE @DBName varchar(40)

--loop code goes here somewhere

EXEC(&#39;USE &#39; + @DBName + &#39; EXEC <dbname>.dbo.<procname>&#39

Hope that helps,

Karl




------------
AAA at 6/12/2002 4:27:12 PM


Hi

I need to run a stored procedure on each database in my SQL server. I want to have a loop to go through each db.

Is there a way I can run &#39;Use @dbname&#39;, I tried Execute and sp_executesql but it didn&#39;t work.

I want to execute the SP withing each db.

Thanks