Truncate Tables using SQL Script
Lately I was preparing an SQL script to clean up Database tables before moving it to production server. The Database contains Lookup tables that should be kept as is, that's why I had to prepare the script to address specific tables.
I used the ordinary SQL statement to delete records from the tables: DELETE FROM table_name. But this statement will not reset the primary Identity column so I had to work on something else. The script should delete all records and reset the identity taking into consideration the dependency between the tables (the forgein keys).
So I prepared the following stored procedure:
CREATE PROCEDURE [dbo].[TableTruncate]
@TableName NVARCHAR(128)
AS
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
BEGIN TRAN
DECLARE @NextId NUMERIC = CASE WHEN (IDENT_CURRENT(@TableName) = 1) THEN 1 ELSE 0 END
DECLARE @Sql NVARCHAR(MAX) = 'DELETE FROM [' + @TableName + ']'
EXECUTE sp_executesql @Sql
IF (@@ERROR = 0) BEGIN
-- error
DBCC CHECKIDENT (@TableName, RESEED, @NextId)
COMMIT TRAN
END ELSE BEGIN
-- Error
ROLLBACK
END
So the truncation script will look like:
exec [dbo].[TableTruncate] 'Table1'
exec [dbo].[TableTruncate] 'Table2'
....
....
....
I hope this script will be useful for you.