Change database within a stored procedure

[Mirrorball]

I have an update stored procedure which needs to be run over 40 identical databases on the same server. Is it possible to include a command in the update stored procedure to change from one database to another?

Thanks for your help

 

[stsuing]

USE
Changes the database context to the specified database.

Syntax
USE { database }

Arguments
database

Is the name of the database to which the user context is switched. Database names must conform to the rules for identifiers.

 

[mukund]

Hi Mirrorball,
You can do updates without changing the database. The trick is to prefix the table you want to update with the database name.
e.g.
Update databasename..TableName set ColumnName = ColumnValue where condition

-Mukund.

 

[tlbroadbent]

The Use Database statement DOES NOT work within a stored procedure.

Mukund is correct. You can use the database name in the SQL statement, but this could require 40 statements and I assume you want to execute the same statment 40 times. You can overcome this with dynamic SQL.

Another alternative is to utilize the undocumented stored procedure named sp_MSforeachdb that will execute statements within the context of each database. Here is an example.

Declare @sql varchar(1000)
Set @sql = 'If Exists (Select * From ?.dbo.sysobjects Where type=''U'' and name=''MyTable'' and uid=1) ; Update ?.dbo.MyTable Set col1 = 0 Where col2<100'

exec sp_msforeachdb @command1 = @sql

Hopefully, I've got the correct syntax. Check out this SP and others at ...

http://www.zarr.net/vb/sql/undocumented.asp#sp_msForEachDB

Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions.