WilberBeast

Often as part of an ETL process I want to simply clear out all the table within a schema, e.g. ‘landing’ or ‘etl’.

However, as new tables are added to the schema, the ‘clear out’ code needs to be maintained. In addition, if any referential integrity exists, the tables will need to be cleared in a certain order. Finally, the Truncate Table statement is significantly faster than a simple Delete, but there are restrictions on when it can be used.
All of which led me to create the following stored procedure so that I don’t have to think these concerns again, allowing me time to focus on the core of the ETL work.

Feel free to use/copy/amend as appropriate, but please let me have your comments/updates.

Create Procedure etl.ClearSchemaTables
/******************************************************************************
Description:
    Clears down any tables in the target schema.
    Tables are cleared down in referential order using truncate where possible.
In:
    @target_schema_name
        The name of the schema whose tables should be cleared, e.g. 'landing'.
History:
    03-Aug-2015 S.Wilber
        Created
******************************************************************************/
    @target_schema_name sysname
As

Set         NoCount On

-- declare variables
Declare     @object_id int
           ,@table_name sysname
           ,@schema_name sysname
           ,@is_referenced bit
           ,@success bit
           ,@sql nvarchar(max)

If Object_ID('tempdb..#table_dependencies') Is Not Null Drop Table #table_dependencies
Create Table #table_dependencies
(
    source_object_id int
   ,dependancy_object_id int
)

-- declare tables
If Object_ID('tempdb..#tables') Is Not Null Drop Table #tables
Create Table #tables
(
    object_id int
   ,schema_name sysname
   ,table_name sysname
   ,is_referenced bit default(0)
   ,is_processed bit default(0)
)

-- find all tables in the schema - look for fk references that are not from self
Insert      #tables (object_id, schema_name, table_name, is_referenced)
Select      t.object_id
           ,s.name
           ,t.name
           ,Case When fk.object_id Is Not Null Then 1 Else 0 End
From        sys.tables t
Inner Join  sys.schemas s On (s.schema_id = t.schema_id)
Left Join   sys.foreign_keys fk On (fk.referenced_object_id = t.object_id and fk.parent_object_id != t.object_id)
Where       s.name = @target_schema_name

-- retrieve all interdependancies between batch managed tables.
Insert      #table_dependencies (source_object_id, dependancy_object_id)
Select      fk.parent_object_id
           ,fk.referenced_object_id
From        sys.foreign_keys fk
Inner Join  #tables s On (s.object_id = fk.parent_object_id)
Inner Join  #tables t On (t.object_id = fk.referenced_object_id)

-- loop through all the tables delete the content, either via a truncate or delete depending on foreign keys
While Exists (Select 1 From #tables Where is_processed = 0)
  Begin
  
    -- find the first table where all of the dependant tables have been processed
    Select      @object_id = object_id
               ,@schema_name = schema_name
               ,@table_name = table_name
               ,@is_referenced = is_referenced
    From        #tables sbt
    Where       is_processed = 0
    And         Not Exists
                (
                    Select      1
                    From        #table_dependencies td
                    Inner Join  #tables tbt On (tbt.object_id = td.source_object_id)
                    Where       td.dependancy_object_id = sbt.object_id
                    And         tbt.is_processed = 0
                )

    -- if none found, then raise an error as it seems that we have a circular dependancy that we can't resolve
    If @object_id Is Null
      Begin
        RaisError ('A table where all dependant tables have been processed cannot be found. This implies that circular dependencies exist. Batch rollback cannot continue.', 11, 0)
      End

    Begin Try
        -- if referenced then we need to perform a delete otherwise we can perform a truncate
        If @is_referenced = 1 
          Begin
            Set         @sql = 'Delete From [' + @schema_name + '].[' + @table_name + ']'
          End
        Else
          Begin
            Set         @sql = 'Truncate Table [' + @schema_name + '].[' + @table_name + ']'
          End
          
        -- exeute the sql
        Exec        sp_executesql @stmt = @sql          
    
    End Try
    Begin Catch
        -- flag as unsuccessful
        Set         @success = 0

        -- declare variables
        Declare         @error_description varchar(2000)
                       ,@error_severity int
                       ,@error_state int

        -- fetch the error details
        Select         @error_description = ERROR_MESSAGE(), @error_severity = ERROR_SEVERITY(), @error_state = ERROR_STATE()

        -- throw the error
        RaisError (@error_description, @error_severity, @error_state) 
            
    End Catch
    
    -- update the table to state that that it has been processed
    Update      #tables
    Set         is_processed = 1
    Where       object_id = @object_id
    
  End