Cursor and USE statment with variables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Cursor and USE statment with variables

Author

Topic

changcg
Starting Member

2 Posts

Posted - 2013-09-18 : 12:26:13

Hello,
I have a cursor that goes through a table with the names of all the database in my server. So for each fetch, the cursor gets the name of a database and assign it to a variable, @databaseName, and try to do some queries from that database by using the command "USE @databaseName". But "USE" doesn't take the variable @databaseName; it is expecting a database name (i.e. USE master).
Here is my code:

Declare @databaseName varchar(50)
Declare c_getDatabaseName CURSOR for SELECT name from tblDatabases
OPEN c_getDatabaseName
FETCH NEXT from c_getDatabaseName into @databaseName
While @@FETCH_STATUS = 0
BEGIN
USE @databaseName <-- here is the problem
GO
-- more code here
-- more code here

END
CLOSE c_getDatabaseName
DEALLOCATE c_getDatabaseName
GO

Any Ideas on how to get USE to take the variable value ?
Any help will be greatly appreciated. Thanks in advance.
changcg

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-09-18 : 12:52:58

You may be able to make use of the undocumented sp_MSforeachdb http://www.databasejournal.com/features/mssql/article.php/3441031/SQL-Server-Undocumented-Stored-Procedures-spMSforeachtable-and-spMSforeachdb.htm

If that does not work for you, the other alternative would be to use dynamic SQL.