How to: Truncate Multiple Tables In SQL Server and the magic of sp_MSforeachtable

SQL
September 7, 2007

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.

1 thought on “How to: Truncate Multiple Tables In SQL Server and the magic of sp_MSforeachtable”

  1. MMJZ says:

    Ok, but how to add a condition for the truncate query?!!!
    I want to add an extended properties to each table and TRUNCATE only the table have a specific value

    for example: extended properties “isRecoverable” and TRUNCATE table with “isRecoverable” value equal “true”

Leave a Reply

Your email address will not be published. Required fields are marked *