How to: Truncate Multiple Tables In SQL Server and the magic of sp_MSforeachtable
SQLSeptember 7, 2007
keithrull
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.
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”