Developing For .NET

In the previous article Clearing a SQL Server Database I wrote about my adventures in writing a Stored Procedure to clear all the data from all the tables in a SQL Server database.  I also said I’d be writing about using Stored Procedures in Entity Framework next, but I’ve belayed that for a while, so my apologies to anyone who is dying to read that article.

More than one way to skin a Table

My buddy Stuart posted another way to do this in the comments:

Hey Joel,

I would generalize the script so you don’t have to keep updating the stored proc each time you add another table. This is a script that I’ve used that does the same thing (but gets around having to order the tables by disabling the constraints first):

– disable referential integrity
EXEC sp_MSForEachTable ‘ALTER TABLE ? NOCHECK CONSTRAINT ALL’
GO

EXEC sp_MSForEachTable ‘
IF OBJECTPROPERTY(object_id(”?”), ”TableHasForeignRef”) = 1
DELETE FROM ?
else
TRUNCATE TABLE ?
‘
GO

– enable referential integrity again
EXEC sp_MSForEachTable ‘ALTER TABLE ? CHECK CONSTRAINT ALL’
GO

– Reseed each table
EXEC sp_MSForEachTable ‘
IF OBJECTPROPERTY(object_id(”?”), ”TableHasIdentity”) = 1
DBCC CHECKIDENT (”?”, RESEED, 0)’
GO

Cheers,
Stuart

He makes a great point: what if I, or some other developer, comes along later and adds a table, but forgets to add it to the Stored Procedure?  I’m not actually sure what the consequences would be, so why take the chance?

What’s all this doing?

If you are an old hat at SQL Server, you probably are thinking “So what?”, but I have professed several times that I am a SQL Server newbie, so I found this really cool.  Here are the things I learned about thanks to this little gem:

sp_MSForEachTable 

According to my Bing research, this is an “undocumented feature” built in to SQL Server.  Since “undocumented” really means “unsupported”, the caveat here is that you shouldn’t rely on it because Microsoft could change it or pull the plug without notice.  Personally, given the wealth of information out there about this procedure, I’ve decided to take my chances.

This procedure will loop through every Table in the current database and execute the given statement against it.  See the “?” part?  That is where the Table identifier will be inserted into each statement.  So in the example above, the first thing we do is loop through all the tables and remove the Constraints.  These were the pesky things in the previous article that prevented us from using TRUNCATE.  Then we loop through again and clear the tables, using TRUNCATE when we can and DELETE when we must.  Then we reinstate the Constraints and reseed the Identity Columns. 

All in all, this is a very nice bit of work and has really opened my eyes to the power of these SQL Server goodies.  You can bet I’ll be returning to this topic in the future.

sp_MSForEachDB 

On a side note, there is a similar method that will loop through and operate the specified statement against every Database as well.  This feels a lot more scary to me, and I am unlikely to use it anytime soon, but it is cool knowing it is there.

OBJECTY_ID and OBJECT_PROPERTY

You’ll also notice that we use a built in function called OBJECT_ID.  Basically, OBJECT_ID retrieves a handle to the Table itself, which we can then pass to other functions to retrieve information about the table.

In the example above, we are passing this handle to another function called OBJECT_PROPERTY.  This allows us to retrieve a metadata property containing information about the Table in question.  We use it twice above, once to check the "TableHasForeignRef" property and again to check the "TableHasIdentity" property.  These values are then used to further determine whether or not to execute specific actions against these tables.

But what if I don’t want ALL the tables?

I actually ran into this issue pretty quickly: in my project I have a table that holds some configuration information.  It’s not in my diagram because it isn’t related to any of the other tables, it’s just holding data.  The problem I ran into was that this table was getting cleared along with the rest, but it needs to retain its data when the other tables are cleared. 

The configuration table wasn’t getting cleared previously because it wasn’t specifically listed in the original Stored Procedure.  Now that we are doing things more dynamically, however, it got caught in the cross fire and cleared out.  Naturally, this created an assortment of “unintended consequences” the first time the software was run following a CLearDatabase command.

So the question became “how do I execute against all tables except this one?”  I needed a way to ignore that table.  Fortunately, part of the answer was right in front of my face: OBJECT_ID.

Ignoring a specific Table

I realized I should be able to use the same OBJECT_ID function to specify which Table I wanted to skip, I just didn’t know what other function to use.  I did a little Binging and found the OBJECT_NAME function, but I had a little trouble getting the result to match a string I was using to specify the Table name.  A little more digging led me to an example that allowed me to query the database and see metadata about the table.  To display the Table name, it used another function called QUOTENAME, which had as a parameter the results of OBJECT_NAME.  So I strung all this together and came up with the following:

IF QUOTENAME(OBJECT_NAME(object_id("?"))) != ”[Configuration]”

Where "Configuration” is the name of the Table I want to ignore.  Notice the use of the Square Brackets: QUOTENAME always seems to return the Table name wrapped in Square Brackets.  I’ve seen these before in other SQL Server samples, but I don’t know yet what their significance is, I just know that adding them made this work.

Also, since it may not come across too clearly, those are paired single quotes (or Tics) around the Table name.  I don’t know if it matters, perhaps regular double quotes would be fine, I just know that in the final version that worked that is what I used.

The only part of the procedure that needs this is the part that TRUNCATEs or DELETEs, so here is the updated version of that part:

EXEC sp_MSForEachTable
‘
IF QUOTENAME(OBJECT_NAME(object_id("?"))) != ”[Configuration]”
IF OBJECTPROPERTY(object_id("?"), "TableHasForeignRef") = 1
DELETE FROM ?
else
TRUNCATE TABLE ?
‘
;

Thanks

So far this has worked very well, and I now know that there are a lot of options open to me in SQL Server for solving these kinds of problems.  I plan to spend some time getting to know more of them very soon.  There is plenty of stuff to learn in the Transact-SQL Reference on MSDN.

A Special Thanks to Stuart for sharing, I love a spirit of co
llaboration: “Good on ya, Mate!”