Help with USE command 1

[PaulSc]

Hello,
We have a number of SQL 7.0 databases where we want to perform the same task on each one, so we have written a stored procedure to perform the task.

The stored procedure uses a USE command to point it at the appropriate database (defaulting to master), which it obtains from a cursor which extracts the database names from sysdatabases, however when the db name is placed into a variable and then the variable passed to the USE command it doesnt work.

i.e USE @DB_Name doesnt do anything.

If the EXEC method 'EXEC ("USE " + @DB_Name)' function is used it works ok but once the EXEC has completed the Db returns to the default db and any subsequent refrences to DB_Name use Master...

Has anybody any suggestions as to how we may achieve this sort of routine or where we may be going wrong?

Thanks.

 

[Bernadette]

Hi There Paul

You should use the undocumented stored procedure sp_msforeachdb. It is much easier.

Here is an article from the internet about it

sp_MSForEachDB


SQL Server includes an un-documented stored procedure that can make performing on the fly tasks against all databases on a single server easier. This command should NEVER be used in a production environment but can be of use for quickly executing code in a development or test environment.

The procedure uses a cursor to loop through the sysdatabases table and execute an inputted command against each database on that system. While this would be a fairly trival task for someone to write, this procedure already exists so can save you time for those one off tasks.

At a basic level you can simply execute this stored procedure with a TSQL command as a parameter. Place a ? where the database name should be located.

e.g. Run the DBCC command against every database on the server.

exec sp_MSForEachDB ‘DBCC CheckDB(?)’

or Quickly backup all databases on a server.

exec sp_MSForEachDB "backup database ? to DISK='c:\winnt\temp\?.bak'"

Note: You are likely to get an error stating TempDB cannot be backed up. As TempDB is recreated from model each time the server is restarted this can safely be ignored.

However the functionality of this stored procedure doesn’t end here. The parameters to the procedure are as follows

sp_MSForEachDB <command1>, <replacementcharacter>,<comand2>, <command3>, <precommand>, <postcommand>

The <command> parameters are the TSQL command you wish to perform against each database and you can have three. The replacement character tells this stored procedure which character in the command strings are to be replaced with the name of each database and this defaults to “?”. The <precommand> is a command to run before the other commands are executed against each database. The <postcommand> is run after the database commands have finished running against each database.

e.g.
Show which database is being maintained, run the DBCC check db on the database then backup the database.

exec sp_MSForEachDB &quot;select 'Performing Maintance for DB: *'&quot;,'*','DBCC CheckDB(*)',&quot;backup database * to DISK='c:\winnt\temp\*.bak'&quot;

Hope This Helps Bernadette