Undocumented Stored Procedure 1 – sp_MSForEachDB
There are a number of reasons that you might need to run the same (or similar) bits of SQL against a number of similar databases. I find myself doing this when I update some part of an application that runs against a number of individual client databases on the same server. In my case, the databases are consistently named in the format ‘client’ + ‘ApplicationName – i.e. ReflectivePerspectiveBLOG
sp_MSForEachDB is a built in SQL Server stored procedure which is undocumented in Books Online, but helps us out in this situation, allowing us to run a command on each database on the server
EXEC sp_MSForEachDB @command1 = '
if (db_name() like '%BLOG')
--Perform our operation
update foo set bar = 1 where wibble=2
Undocumented Stored Procedure 2 – sp_MSForEachTable
Another related stored proceedure is sp_MSForEachTable – this one allows you to do things to each table in the database:
EXEC sp_MSForEachTable @command1 = '
select top 10 * from ?
SET vs SELECT to initialize variables
There are two ways of allocating values to variables in TSQL – SET and SELECT. Many people get into the habit of using SET to initialize their variables, like so
SET @foo = 1
SET @bar = 2
SET @wibble = 3
For performance purposes this is quite in-efficient as each use of SET is the equivaent of a SELECT statement.
SELECT, on the other hand has the advantage that it is able to set multiple variables at once:
SELECT @foo = 1, @bar = 2, @wibble=3
This single select statement is the equivalent of one of the SET statements above. The performance advantages of this increase when allocating values to variables from database tables:
SET @foo = (select foo from table where 1=1)
SET @bar = (select bar from table where 1=1)
does the same as the following statement, but only executes the query against the database once.
SELECT @foo = foo, @bar = bar from table where 1=1
Table variables are very useful and often forgotten about feature of SQL Server. You can use them much as you might a temporary table, however they have the added benefit that they will clean up after them selves, so you don’t have to remember to do the drop table #foo.
You can create Table Variables like so:
declare @tableVar table (col1 int, col2 int)
and from then on you can use them much as you would any other table
insert into @tableVar (col1, col2) values(1,2)
select * from @tableVar