SQL Server – Undocumented Stored Procedure sp_MSforeachtable

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: [more]

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.

ASP.NET – Health Monitoring and EventLogWebEventProvider – Part 1

The ASP.NET health monitoring enables you to add instrumentation to Web applications by using the so called Web Events. These Web events give us information about health status.

You can configure health monitoring by setting events and providers in the healthMonitoring section.

Naturally, ASP.NET provide us with a few out-of-the-box providers such as the EventLogWebEventProvider.

As many of you may have already notice, when using the EventLogWebEventProvider the events are added to the Application EventLog with the following source pattern:

        ASP.NET "framework version"

If you are using ASP.NET 2.0 the source will look similar to "ASP.NET 2.0.50727.0 ".

You can imagine what happen when a server hosts several web applications … you can’t easily figure which application raised a web event because you can’t apply a filter to do that. To figure it out you must inspect the eventlog entry data.

If you think this is wrong and that Microsoft should do something about it, go here and here, vote and comment.

What can you do to overlap this? Well you can create your own EventLogWebEventProvider that allows you to specified which Source to use.

Doing such a provider is fairly simple but lead us to THE problem: which eventId to use when creating the EventLog entry?

What? Why is this a problem? are you saying.

Well, lets start all from the beginning … you want to create your own provider so you can specify the EventLog source but you certainly desire to keep the remaining settings unchanged so that monitoring applications that track the EventLog entry for well known eventIds still working fine.

The problem is that Microsoft don’t expose the algorithm used to created the eventId from the WebEvent data, and this way we can only guess which eventId to use.

If you look at EventLogWebEventProvider.ProcessEvent method you will find the following code:

int num = UnsafeNativeMethods.RaiseEventlogEvent((int) type, (string[]) dataFields.ToArray(typeof(string)), dataFields.Count); 

This is your black box, no source or information is available.

To guess which eventId is used for a specific Web Event I created a small page that raises all known Web  Events.

I found that even with all known Web Events configured to use EventLogWebEventProvider almost half of them don’t appear in EventLog, but those that have an EventLog entry made me speculate that eventIds are sequential and follow the classes hierarchy. Here are the results:

Web Event EventLog entry eventId Speculated eventId range
WebBaseEvent 1303
WebManagementEvent 1304
WebApplicationLifetimeEvent 1305 1305
WebRequestEvent 1306 1306
WebHeartbeatEvent 1307 1307
WebBaseErrorEvent 1308
WebRequestErrorEvent 1309 1309
WebErrorEvent 1310 1310
WebAuditEvent 1311
WebSuccessAuditEvent 1312 1312
WebAuthenticationSuccessAuditEvent 1313
WebFailureAuditEvent 1314 1314
WebAuthenticationFailureAuditEvent 1315
WebViewStateFailureAuditEvent 1316 1316

 

Please note that I’m considering that no two different Web Events share the same eventId.

If you believe the assumptions made are correct you can now start coding your provider.

Remember that you must create the EventLog source before  use it. You can do this by using the EventLog.CreateEventSource method.