How to Truncate only the Selected Tables from Database
How to Truncate only the Selected Tables from Database? This stored procedure helps you out in truncating the selected tables. These tables has to be passed as a string parameter to the stored procedure.
We came up with a stored procedure. Here we go.
--EXEC USP_Truncate_Tabs '''MEMBER_DIM'',''CLAIM_FACT'',''CLAIM_TYPE_DIM'',''POS_TYPE_DIM'''
CREATE PROC USP_Truncate_Tabs(@String VARCHAR(7500))
AS
BEGIN
SET NOCOUNT ON
DECLARE @Constraints TABLE(ID INT IDENTITY,DropStmt VARCHAR(1000),CreateStmt VARCHAR(1000))
DECLARE @Tab TABLE(ID INT IDENTITY,TabName VARCHAR(200))
DECLARE @SQL1 VARCHAR(8000)
SET @SQL1='SELECT NAME FROM sysobjects WHERE TYPE=''U'' AND name in('+(@String)+')'
/** Getting only selected tables details into @Tab **/
INSERT INTO @Tab EXEC(@SQL1)
/** Insert all Create and Drop Constraint Statements into a Temp Table *******/
INSERT INTO @Constraints (DropStmt,CreateStmt)
SELECT
'ALTER TABLE ['+ ForeignKeys.ForeignTableSchema +
'].['+ ForeignKeys.ForeignTableName +
'] DROP CONSTRAINT [' + ForeignKeys.ForeignKeyName + ']; '
,'ALTER TABLE ['+ ForeignKeys.ForeignTableSchema +
'].['+ ForeignKeys.ForeignTableName +
'] WITH CHECK ADD CONSTRAINT ['+ ForeignKeys.ForeignKeyName +
'] FOREIGN KEY(['+ ForeignKeys.ForeignTableColumn +
']) REFERENCES [' + schema_name(sys.objects.schema_id) + '].[' +
sys.objects.[name] + ']([' +
sys.columns.[name] + ']); '
from sys.objects
inner join sys.columns
on (sys.columns.[object_id] = sys.objects.[object_id])
inner join (
select sys.foreign_keys.[name] as ForeignKeyName
,schema_name(sys.objects.schema_id) as ForeignTableSchema
,sys.objects.[name] as ForeignTableName
,sys.columns.[name] as ForeignTableColumn
,sys.foreign_keys.referenced_object_id as referenced_object_id
,sys.foreign_key_columns.referenced_column_id as referenced_column_id
from sys.foreign_keys
inner join sys.foreign_key_columns
on (sys.foreign_key_columns.constraint_object_id
= sys.foreign_keys.[object_id])
inner join sys.objects
on (sys.objects.[object_id]
= sys.foreign_keys.parent_object_id)
inner join sys.columns
on (sys.columns.[object_id]
= sys.objects.[object_id])
and (sys.columns.column_id
= sys.foreign_key_columns.parent_column_id)
) ForeignKeys
on (ForeignKeys.referenced_object_id = sys.objects.[object_id])
and (ForeignKeys.referenced_column_id = sys.columns.column_id)
where (sys.objects.[type] = 'U')
and (sys.objects.[name] not in ('sysdiagrams'))
/*** Drop all constraints in Database ****/
DECLARE @Sql VARCHAR(1000)
DECLARE @i INT,@Max INT
SELECT @Max=COUNT(1)FROM @Constraints
SET @i=1
WHILE(@i<=@Max)
BEGIN
SELECT @Sql=DropStmt FROM @Constraints WHERE ID=@i
EXEC(@Sql)
IF(@@ERROR<>0)
BEGIN
PRINT'Failed to Drop the Constraint : '+@Sql
END
SET @Sql=''
SET @i=@i+1
END
PRINT'All foreign keys are dropped successfully'
/*** Truncate selected Tables ****/
DECLARE @TabName VARCHAR(200)
SELECT @Max=COUNT(1)FROM @Tab
SET @i=1
WHILE(@i<=@Max)
BEGIN
SELECT @TabName=TabName FROM @Tab WHERE ID=@i
SELECT @Sql='TRUNCATE TABLE '+@TabName
EXEC(@Sql)
IF(@@ERROR<>0)
BEGIN
PRINT'Failed to Run the statement : '+@Sql
END
PRINT'Table '+@TabName+' Truncated'
SET @Sql=''
SET @i=@i+1
END
PRINT'All tables are truncated successfully'
/*** Recreate all constraints in Database ****/
SELECT @Max=COUNT(1)FROM @Constraints
SET @i=1
WHILE(@i<=@Max)
BEGIN
SELECT @Sql=CreateStmt FROM @Constraints WHERE ID=@i
EXEC(@Sql)
IF(@@ERROR<>0)
BEGIN
PRINT'Failed to Recreate the Constraint : '+@Sql
END
SET @Sql=''
SET @i=@i+1
END
PRINT'All foreign keys are recreated successfully'
END
/*** Execute SP to truncate the tables Emp, Dept, Emp_Add ***/
EXEC USP_Truncate_Tabs '''Dept'',''Emp'',''Emp_Add'''
Output is like below
