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 = '

use [?]

if (db_name() like '%BLOG')

BEGIN

     --Perform our operation

     update foo set bar = 1 where wibble=2

END

'

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

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
Technorati Tags: , , ,

4 Comments »