Recently I was in need of a script for SQL Server 2005 that would go in and truncate all of my tables in a given database. Not too tall an order one would think. Just write the darn thing and off you go. Not so fast, Bucko. What about those pesky foreign-keys? What about tables I don’t want truncated? What about the tedium in putting this thing together?
Well, after Googling and Googling and Googling some more I found this little jewel written by pvsramu in a forum entry over at SQLTeam.com. I thought I had found the answer…and I did, almost.
It did everything I wanted except I wanted to be able to exclude tables from the truncation. So mods were in order. I started looking at the best way to create a variable that I could use to list the tables to be excluded. All I found was frustration. SQL Server is not very kind when it comes to using comma separated list variables in an “IN” clause. After looking around for a while and, frankly, running out of time, I decided the best course of action was brute force. Not pretty but it gets the job done. With the whole brute force thing in mind, all it takes to remove tables from the truncation process is to add a list of them to each of the four queries in the script. Like I said, it’s not to elegant.
I certainly welcome any thoughts you all might have as to how to get SQL Server to deal with a list of values in a variable so that I could have just one list in the script.
As for this script, we use it daily. It’s been very handy and I hope some of you might find it handy as well.
Until later…
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[truncate_tables]
-- Add the parameters for the stored procedure here
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
/*
This batch t-sql deletes data from all the tables in the database.
Here is what it does:
1) Disable all the constraints/triggers for all the tables
2) Delete the data for each child table & stand-alone table
3) Delete the data for all the parent tables
4) Reseed the identities of all tables to its initial value.
5) Enable all the constraints/triggers for all the tables.
Note: This is a batch t-sql code which does not create any object in database.
If any error occurs, re-run the code again. It does not use TRUNCATE statement to delete
the data and instead it uses DELETE statement. Using DELETE statement can increase the
size of the log file and hence used the CHECKPOINT statement to clear the log file after
every DELETE statement.
Imp: You may want to skip CHECKIDENT statement for all tables and manually do it yourself. To skip the CHECKIDENT,
set the variable @skipident to "YES" (By default, its set to "NO")
Usage: replace #database_name# with the database name (that you wanted to truncate) and just execute the script in query analyzer.
*/
-- use [#database_name#]
SET NOCOUNT ON
DECLARE @tableName varchar(200)
DECLARE @tableOwner varchar(100)
DECLARE @skipident varchar(3)
DECLARE @identInitValue int
DECLARE @tableExclusions varchar(200)
SET @tableName = '
SET @tableOwner = '
SET @skipident = 'NO'
SET @identInitValue=1
/*
Step 1: Disable all constraints
*/
EXEC sp_MSforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
EXEC sp_MSforeachtable 'ALTER TABLE ? DISABLE TRIGGER ALL'
/*
Step 2: Delete the data for all child tables & those with no relationships
*/
WHILE EXISTS
(
SELECT T.table_name
FROM INFORMATION_SCHEMA.TABLES T
LEFT OUTER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
ON T.table_name=TC.table_name
WHERE (TC.constraint_Type ='Foreign Key'
OR TC.constraint_Type IS NULL)
AND T.table_name NOT IN ('dtproperties','sysconstraints','syssegments')
-- add the other tables we want to exclude
AND T.table_name NOT IN (--add values here--)
AND Table_type='BASE TABLE'
AND T.table_name > @TableName
)
BEGIN
SELECT TOP 1 @tableOwner=T.table_schema,
@tableName=T.table_name
FROM INFORMATION_SCHEMA.TABLES T
LEFT OUTER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
ON T.table_name=TC.table_name
WHERE (TC.constraint_Type ='Foreign Key'
OR TC.constraint_Type IS NULL)
AND T.table_name NOT IN ('dtproperties','sysconstraints','syssegments')
-- add the other tables we want to exclude
AND T.table_name NOT IN (--add values here--)
AND Table_type='BASE TABLE'
AND T.table_name > @TableName
ORDER BY t.table_name
--Delete the table
EXEC('DELETE FROM '+ @tableOwner + '.' + @tableName)
--Reset identity column
IF @skipident = 'NO'
IF EXISTS
(
SELECT * FROM information_schema.columns
WHERE COLUMNPROPERTY(OBJECT_ID(
QUOTENAME(table_schema)+'.'+QUOTENAME(@tableName)),
column_name,'IsIdentity')=1
)
BEGIN
SET @identInitValue=1
SET @identInitValue=IDENT_SEED(@tableOwner + '.' + @tableName)
DBCC CHECKIDENT (@tableName, RESEED, @identInitValue)
END
CHECKPOINT
END
/*
Step 3: Delete the data for all Parent tables
*/
SET @TableName='
SET @tableOwner='
WHILE EXISTS
(
SELECT T.table_name
FROM INFORMATION_SCHEMA.TABLES T
LEFT OUTER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
ON T.table_name=TC.table_name
WHERE TC.constraint_Type ='Primary Key'
AND T.table_name <>'dtproperties'
-- add the other tables we want to exclude
AND T.table_name NOT IN (--add values here--)
AND Table_type='BASE TABLE'
AND T.table_name > @TableName
)
BEGIN
SELECT TOP 1 @tableOwner=T.table_schema,
@tableName=T.table_name
FROM INFORMATION_SCHEMA.TABLES T
LEFT OUTER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
ON T.table_name=TC.table_name
WHERE TC.constraint_Type ='Primary Key'
AND T.table_name <>'dtproperties'
-- add the other tables we want to exclude
AND T.table_name NOT IN (--add values here--)
AND Table_type='BASE TABLE'
AND T.table_name > @TableName
ORDER BY t.table_name
--Delete the table
EXEC('DELETE FROM '+ @tableOwner + '.' + @tableName)
--Reset identity column
IF @skipident = 'NO'
IF EXISTS
(
SELECT *
FROM information_schema.columns
WHERE COLUMNPROPERTY(OBJECT_ID(QUOTENAME(table_schema)+'.'+QUOTENAME(@tableName)),
column_name,'IsIdentity')=1
)
BEGIN
SET @identInitValue=1
SET @identInitValue=IDENT_SEED(@tableOwner + '.' + @tableName)
DBCC CHECKIDENT (@tableName, RESEED, @identInitValue)
END
CHECKPOINT
END
/*
Step 4: Enable all constraints
*/
EXEC sp_MSforeachtable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
EXEC sp_MSforeachtable 'ALTER TABLE ? ENABLE TRIGGER ALL'
SET NOCOUNT OFF
END
DougHughes.net 