I'm not an every day SQL Server user but I use SQL Server regularly since 7.0 version until the 2005 version (not yet tried 2008 in a serious way) and from time to time I still find some nice hidden gems.
A few days ago I needed to created a cleanup script for an application and one of the tasks was to drop all tables that match a specific name pattern.
My first thought was to use a cursor to loop or a dynamic SQL statement ...
... but this time I decided to google for some other approach, and I found the amazing undocumented sp_MSforeachtable stored procedure from the master database.
It does the same but it requires considerably less code and improves the script readability.
Below is the syntax for calling the sp_MSforeachtable SP:
exec @RETURN_VALUE=sp_MSforeachtable @command1, @replacechar, @command2,
@command3, @whereand, @precommand, @postcommand
Where:
@RETURN_VALUE - is the return value which will be set by "sp_MSforeachtable" @command1 - is the first command to be executed by "sp_MSforeachtable" and is defined as a nvarchar(2000) @replacechar - is a character in the command string that will be replaced with the table name being processed (default replacechar is a "?") @command2 and @command3 are two additional commands that can be run for each table, where @command2 runs after @command1, and @command3 will be run after @command2 @whereand - this parameter can be used to add additional constraints to help identify the rows in the sysobjects table that will be selected, this parameter is also a nvarchar(2000) @precommand - is a nvarchar(2000) parameter that specifies a command to be run prior to processing any table @postcommand - is also a nvarchar(2000) field used to identify a command to be run after all commands have been processed against all tables
As you can see, this stored procedure offer us some flexibility, but for the most common uses you will only use one or two of them.
Back to my problem, drop all tables with a specific naming pattern, I ended up using a script just like this:
declare @appName varchar(128)
declare @mycommand varchar(128)
declare @mywhereand varchar(128)
set @appName = 'xpto'
set @mycommand = 'drop table ?'
set @mywhereand = 'and o.name like ''' + @appName + '__Log__%'' escape ''_''
print 'Dropping all tables belonging to ' + @appName + ' application ...'
exec sp_MSforeachtable
@command1 = @mycommand,
@whereand = @mywhereand
What I'm saying here is that the command 'drop table' should be executed for every table that match the criteria name like 'xpto_Log_%'.
As you can see its fairly simple and clean and this is just the top of the iceberg.
For more detail about sp_MSforeachtable go here and here.
