Changing database within a stored proc

Is this possible in a stored procedure to receive a database name in parameter to be able to apply the code within that stored procedure to all table in the database?

I need to go trough all of the database of the server trough the database list of the master and gather data from all the table and i was wondering if there was a way for me to do a generic code for each database, the only thing that i would need to change would be the database it look into at the moment.

Would something like this work? :

*Code*

*a loop in the database list*

My_stored_proc current_database_name

My stored proc:

Use current_database_name

*Code that work with this database*

Unfortunately, that is not just a simple task.

Yes, you can create a loop through the databases from sys.databases (SQL 2005) or sysdatabases (SQL 2000).

However, you cannot simply use an 'Object' name as you would use a variable or parameter. So the statement [ USE @.Variable ] does not work. You could create an entire SQL statement as a variable, and then execute that statement using dynamic SQL -but that too has it issues.

First you have to 'build' the entire SQL statement; that's not too difficult. something like (peusdocode):

Code Snippet

DECLARE
@.dbName nvarchar(200),
@.SQLStatement nvarchar(1000)
{loop here}
SET @.dbName = name in sys.databases {selected from loop position}
SET @.SQLStatement = 'Use ' + @.dbname
EXECUTE( @.SQLStatement )

Now at this point, there is a problem, because the EXECUTE (or sp_executesql) statement operates in a separate thread, and any results are not directly accessible to the rest of your code. The next line of your code would NOT use any database other then the one you started out with because as soon as the EXECUTE statement completes, control is returned back to the original thread -which has no idea what happened when the EXECUTE statement ran.

There are methods to get around that behavior, but the most common ones require extensive knowledge of how EXECUTE() or sp_executesql operates.

I recommend that you first review this article, and then come back for assistance if you wish to continue down this path.

Dynamic SQL - The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html

The key element that I hope you get here is, yes, someone can create the functionality. But will you be able to understand and maintain the code, or will you be 'up a creek' if something changes? ... Your best option if you have to do this, is to learn and understand how to use and what is happening with dynamic SQL as it is being used. Without that understanding, you may continually be frustrated.

|||

Thanks for the text, it was very interesting.

I found out that using cursor will be much more usefull for going trough all the table of the databases.