Last month, we looked at how you can retrieve table information from different databases within a SQL Server instance. ("How Not to Use USE," InstantDoc ID 48968). This month, we look at how to execute statements in the context of a user database.We know that we can't embed GO statements because they aren't a T-SQL language construct, but we need to execute some statements (e.g., DBCC
CHECKTABLE) in the context of the database. How can we write a routine that does this for all user databases? First, we need to be able to switch the database context, which we do by passing multiple statements in to an EXEC statement or sp_executesql call, as the code at callout A in Listing 1 shows. This statement returns the following output:
master
pubs
master
Thus, all statements after the USE statement are executed in the context of the specified database. This technique also works well when you use an EXEC statement like the one that you see in the code at callout B in Listing 1.
Now, let's combine this technique with the technique we used last time to query schema information from each user database. In this case, we'll want to list each user table in each user database, so we're going to use a nested cursor in a procedure like the one we employed in "How Not to Use USE." Listing 1 shows the code with the nested cursor included.
—Gert Drapers
Development Manager
Visual Studio Team System