How to Truncate only the Selected Tables from Database

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

 

Posted in SQL Scripts | Tagged , , , , | Leave a comment
Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments