I’ve been working on alot of SQL Server lately due to the current project i’m assigned to and I found myself this morning needing a query that would truncate all the tables in one of my staging database. My initial thought is that I can do this using a cursor that would hold all truncate statements and execute each one of them one at a time so within 5 minutes i was able to build a query that looks like this:

–declare a variable that would hold the query to be executed
DECLARE @TruncateQuery varchar(4000)

— create a cursor that would hold our truncate statements
DECLARE TruncateQuerries CURSOR LOCAL FAST_FORWARD
FOR SELECT
    N‘TRUNCATE TABLE ‘ +
    QUOTENAME(TABLE_SCHEMA) +
    N‘.’ + QUOTENAME(TABLE_NAME)
FROM INFORMATION_SCHEMA.TABLES
WHERE
        TABLE_TYPE = ‘BASE TABLE’
    AND    OBJECTPROPERTY    (
                            OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
                            N‘.’ + QUOTENAME(TABLE_NAME)
                        ), ‘IsMSShipped’) = 0

— read our truncate statements
OPEN TruncateQuerries
— loop thru each statement in our truncate statement cursor
FETCH NEXT FROM TruncateQuerries INTO @TruncateQuery
WHILE @@FETCH_STATUS = 0
BEGIN
    –execute the statement
    EXEC(@TruncateQuery)
    –assign the current truncate statement to our @TruncateStatement variable
    FETCH NEXT FROM TruncateQuerries INTO @TruncateQuery 

END
— close our cursor
CLOSE TruncateQuerries
— and free up the resources
DEALLOCATE TruncateQuerries

Looks great right? Then I realized what my good friend Jon Galloway told me once that there are hidden stored procedures in SQL Server and 1 of those stored proc is sp_MSforeachtable. It’s an undocumented sp so you won’t find anything about it in the SQL Books Online. What this stored procedure basically does is that it lets you execute a command or a set of commands against all tables inside a database. Before we go into further detail lets look at the parameters that sp_MSforeachtable expects.

exec @RETURN_VALUE=sp_MSforeachtable @command1, @replacechar, @command2,
@command3, @whereand, @precommand, @postcommand

Where:(description taken from [LINK])

• @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

All other parameters are optional except for @command1 which is first statement that would be executed.

Now, lets rewrite our truncate table query from above to use the sp_MSForeachtable procedure:

EXEC [sp_MSforeachtable] @command1=”TRUNCATE TABLE ?”

What??? Tha’t it? 1 line? Crazy huh? I didn’t realized that it was that easy until i tried it. Man, If i knew this when I started writing my query above I could have not wasted 5 minutes of my life in something that can be done in 10 seconds. Thanks for hiding feature this Micrsoft!

But wait! There’s more, now i’ve truncated my tables i need a way to reseed all the identity columns on my database:

EXEC [sp_MSforeachtable] @command1=”DBCC CHECKIDENT (?, RESEED, 100)”

What??? Another 1 liner? Whats even better is that I could also REINDEX all the tables in my DB with one line of beautiful SQL code:

EXEC [sp_MSforeachtable] @command1=”DBCC DBREINDEX(‘?’)”

Wanna show progress message on each execution? Try this version:

EXEC [sp_MSforeachtable] @command1=”RAISERROR(‘DBCC DBREINDEX(”?”) …’,10,1) WITH NOWAIT DBCC DBREINDEX(‘?’)”

Oh, Ma! I can’t believe being a programmer could be this easy. 😉 I hope i could save someone’s precious time by proving this example because i know it would save mine in the future.