When I include a USE statement in a stored procedure, the procedure doesn't work. Is there an alternative to the USE command that I can put into a stored procedure to change the database?
USE is not a T-SQL statement like GO; only tools such as ISQL, OSQL, SQLCMD, Query Analyzer, and SQL Server 2005 Management Studio understand it.You might need the USE command for one of two types of actions:You might want to retrieve table information (e.g. schema information) that resides in each database, or you might need to execute a T-SQL statement in the context of each database or a set of databases.The second type of action is much harder than the first. Because we don't have room here, we'll explain the technique in a future Ask Microsoft column.
However, if you want to retrieve table information from each database, you can use a procedure similar to the example that the T-SQL code in Listing 1 shows, which retrieves the log file of each database. Instead of putting USE into your T-SQL stored procedures for SQL Server 2000, you can use this procedure, which cycles through the list of databases and uses dynamic SQL to collect data in a temporary table and show the data to the user.
—Gert Drapers
Development Manager
Visual Studio Team System
End of Article
