SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Truncate a table that has foreign keys referencing its columns

By Jonathan Roberts,

I needed a procedure reset a Data Warehouse. To do this I needed to truncate all the Dimension, Fact and Bridge tables. This stored procedure allows the fast truncation of any table that has foreign keys referencing at columns on the table. It drops all the foreign keys referencing the table to be truncated, truncates the table, re-creates the foreign keys. 

The call to the stored procedure is:

EXEC dbo.INFTruncateTable 'Orders', 'dbo'

To process an entire database a script can be set up that will process each of the tables:

DECLARE @Debug bit = 1
SET NOCOUNT ON
DECLARE @TABLE_SCHEMA     sysname,
        @TABLE_NAME       sysname
DECLARE @TablesToTruncate cursor
SET @TablesToTruncate = cursor FOR
SELECT t.TABLE_SCHEMA,
       t.TABLE_NAME
  FROM INFORMATION_SCHEMA.TABLES t
 WHERE (   t.TABLE_NAME LIKE 'Fact%'
        OR t.TABLE_NAME LIKE 'Dim%'
        OR t.TABLE_NAME LIKE 'Bridge%'
       )
   AND t.TABLE_SCHEMA = 'dbo'
   AND t.TABLE_TYPE = 'BASE TABLE'
 ORDER BY t.TABLE_NAME
OPEN @TablesToTruncate 
FETCH NEXT FROM @TablesToTruncate INTO @TABLE_SCHEMA, @TABLE_NAME
WHILE @@FETCH_STATUS = 0 BEGIN
    IF @Debug = 1 BEGIN
        PRINT  'EXEC dbo.INFTruncateTable ''' + @TABLE_NAME + ''', ''' + @TABLE_SCHEMA + ''', ' + CONVERT(varchar,@Debug)
        EXEC dbo.INFTruncateTable @TABLE_NAME, @TABLE_SCHEMA, @Debug -- Debug only
    END
    ELSE BEGIN
        EXEC dbo.INFTruncateTable @TABLE_NAME, @TABLE_SCHEMA, @Debug
    END
    FETCH NEXT FROM @TablesToTruncate INTO @TABLE_SCHEMA, @TABLE_NAME
END
CLOSE @TablesToTruncate
DEALLOCATE @TablesToTruncate

Total article views: 482 | Views in the last 30 days: 25
 
Related Articles
FORUM

Debug SP

Debug SP

FORUM

Debug SP

Debug SP

BLOG

test post for debugging

test post for debugging

FORUM

Debugging through procedure

Debugging through procedure

FORUM

Problem In Debugging

Problem In Debugging

Tags
 
Contribute