During the develop and testing of any applications, probably you need to clean up test data, in order to test from scratch.

In SQL Server, you have the TRUNCATE TABLE statement to perform this.

However, the statement execution will fail if the table has references to other tables.

This code snippet allows you to truncate a table, avoiding the problem, by removing the relations, truncating the table and reestablishing the relations.

sp_fkeys

In this code, I use the sp_fkeys system stored procedure, which retrieve the definition of any relationship between tables.

You can find the procedure documentation here.

The procedure is an old one, I mean, it exists in several SQL server versions.

That’s the reason some of the parameters are about owners. In older versions, the objects belong to users, and that is the reason for name them “owners”.

However, consider that when speaking about owners in objects, in fact we are talking about schemas.

The snippet

These are the steps the snippet performs.

• The first step is declaring the schema and the name of the table to be truncated. (We will define how to manage this in a Stored procedure later).

DECLARE 
       @TableName    SYSNAME = 'WebCustomers'
     , @TableSchema  SYSNAME = 'OnLineSales';

• Then, an in-memory table is declared, for the different columns the sp_fkeys stored procedure returns.

DECLARE 
       @FKSTableVarOri TABLE
 (
   [PKTABLE_QUALIFIER]  SYSNAME
 , [PKTABLE_OWNER]      SYSNAME
 , [PKTABLE_NAME]       SYSNAME
 , [PKCOLUMN_NAME]      SYSNAME
 , [FKTABLE_QUALIFIER]  SYSNAME
 , [FKTABLE_OWNER]      SYSNAME
 , [FKTABLE_NAME]       SYSNAME
 , [FKCOLUMN_NAME]      SYSNAME
 , [KEY_SEQ]            SMALLINT
 , [UPDATE_RULE]        SMALLINT
 , [DELETE_RULE]        SMALLINT
 , [FK_NAME]            SYSNAME
 , [PK_NAME]            SYSNAME
 , [DEFERRABILITY]      SMALLINT
);

When retrieving the information of the relationships, the return value uses one row for each column implied ion a relation. So, if a relation is using a composite key, there will be more than one row for the same relation.

It is needed to consolidate this, to define just one entry by relation, with the columns defines as a comma separated list, for the re-creation of them later.

• a second in memory table is defined to process the consolidation.

DECLARE 
       @FKSTableVar TABLE
 (
   [PKTABLE_OWNER]  SYSNAME
 , [PKTABLE_NAME]   SYSNAME
 , [PKCOLUMN_NAME]  SYSNAME
 , [FKTABLE_OWNER]  SYSNAME
 , [FKTABLE_NAME]   SYSNAME
 , [FKCOLUMN_NAME]  SYSNAME
 , [UPDATE_RULE]    SMALLINT
 , [DELETE_RULE]    SMALLINT
 , [FK_NAME]        SYSNAME
)

• Using the sp_fkeys procedure, the snippet get the child relationships of the table to be truncated, and store them in the fist in-memory table.

INSERT INTO @FKSTableVarOri
EXEC [sp_fkeys] 
     @pktable_name = @TableName
   , @pktable_owner = @TableSchema;

Then, by using the STRING_AGG function, the second in-memory table is filled with one row by each relationship.

• The STRING_AGG function, allows you to concatenate values from different rows in one string, using a delimiter between values.

INSERT INTO @FKSTableVar
       SELECT 
           [PKTABLE_OWNER]
         , [PKTABLE_NAME]
         , STRING_AGG(
                      '[' + 
                      [PKCOLUMN_NAME] + 
                      ']', ',') AS [PKCOLUMN_NAME]
         , [FKTABLE_OWNER]
         , [FKTABLE_NAME]
         , STRING_AGG(
                      '[' + 
                      [FKCOLUMN_NAME] + 
                      ']', ',') AS [FKCOLUMN_NAME]
         , [UPDATE_RULE]
         , [DELETE_RULE]
         , [FK_NAME]
       FROM 
          @FKSTableVarOri
       GROUP BY 
           [FK_NAME]
         , [PKTABLE_OWNER]
         , [PKTABLE_NAME]
         , [FKTABLE_OWNER]
         , [FKTABLE_NAME]
         , [UPDATE_RULE]
         , [DELETE_RULE];

• There is a chance no relationships exist. The snippet check if this happen, to proceed directly to the truncation of the table.

DECLARE 
       @References  SMALLINT;
 
SELECT 
    @References = COUNT(*)
FROM 
   @FKSTableVar;
 
IF @References > 0 --there are relationships
  BEGIN

• It is possible that the tables using our candidate table as reference, have rows with referenced values. In that case, it is impossible to truncate the table, or the relationships will be broken. To avoid this, the snippet dynamically creates a script to sum de row-count of all the child tables. In case there are rows, the process aborts with an error.

DECLARE 
       @Counters  NVARCHAR(MAX);
SELECT 
    @Counters = STRING_AGG(
                           '(select count(*) from [' + 
                           [FKTABLE_OWNER] + 
                           '].[' + 
                           [FKTABLE_NAME] + 
                           '])', '+')
FROM 
   @FKSTableVar;
SET @Counters =
                'Select @ForeignRows=' + 
                @Counters;
PRINT @Counters;
DECLARE 
       @ForeignRows  INT;
EXEC [sp_executesql] 
         @Counters
   , N'@ForeignRows int OUTPUT'
   , @ForeignRows OUTPUT;
SELECT 
    @ForeignRows;
IF @ForeignRows > 0
  BEGIN
    RAISERROR(
    'There are dependent rows in other tables', 10, 1);
  END;
   ELSE

• If it is ok to proceed, two dynamically generated scripts is built, using the STRING_AGG function again, one for drop the relationships of the table, and other for recreate them after the truncate.

DECLARE 
       @DropRefs    NVARCHAR(MAX)
     , @CreateRefs  NVARCHAR(MAX);
SELECT 
    @DropRefs = STRING_AGG(
                           'ALTER TABLE [' + 
                           [FKTABLE_OWNER] + 
                           '].[' + 
                           [FKTABLE_NAME] + 
                           '] DROP CONSTRAINT [' + 
                           [FK_NAME] + 
                           ']', ';')
FROM 
   @FKSTableVar;
SELECT 
    @CreateRefs = STRING_AGG(
                             'ALTER TABLE [' + 
                             [FKTABLE_OWNER] + 
                             '].[' + 
                             [FKTABLE_NAME] + 
                             ']  WITH CHECK ADD  CONSTRAINT ['
                             + 
                             [FK_NAME] + 
                             '] FOREIGN KEY(' + 
                             [FKCOLUMN_NAME] + 
                             ') REFERENCES [' + 
                             [PKTABLE_OWNER] + 
                             '].[' + 
                             [PKTABLE_NAME] + 
                             ']  (' + 
                             [PKCOLUMN_NAME] + 
                             ') ' + 
                                     (CASE [DELETE_RULE]
                                 WHEN 1
                                 THEN
                                 ' ON DELETE CASCADE '
                                 ELSE ''
                              END) + 
                                     (CASE [UPDATE_RULE]
                                 WHEN 1
                                 THEN
                                 ' ON UPDATE CASCADE '
                                 ELSE ''
                              END), ';')
FROM 
   @FKSTableVar;

• The script for drop is executed.

        EXEC [sp_executesql] 
             @DropRefs;
END;

• The script for truncate the table is built and executed.

DECLARE 
       @Truncate  NVARCHAR(MAX) =
                  N'TRUNCATE TABLE [' + 
                  @TableSchema + 
                  '].[' + 
                  @TableName + 
                  ']';
 
EXEC [sp_executesql] 
     @Truncate;
 

• Finally, in case there were relationships, the script for recreating is executed, finishing the process.

IF @References > 0
  BEGIN --there are relationships
    EXEC [sp_executesql] 
         @CreateRefs;
  END;

Using the sp_executesql procedure

A special note about the use of sp_executesql system store procedure.
It can just execute a string containing a T-SQL script, but it can use parameters.
For that purpose, the parameters must be declared in a second string parameter and passed one by one as the next parameters.
Notice the string containing the script, as well as the parameters declaration MUST BE nvarchars.

The snippet as store procedure.

Of course, you can define this snippet as a stored procedure in your database during the devlopment process, to facilitate the execution.

I propose you to have it in a separate schema, as well as any other «tool» for your development work.

This can be defined by using the two first variables declared in the snippet as parameters of the procedure.

CREATE PROCEDURE [Development].[TruncateX] 
   @TableName    SYSNAME
 , @TableSchema  SYSNAME = NULL
AS
  BEGIN
    SET NOCOUNT ON;
    SET @TableSchema = ISNULL(@TableSchema, 'dbo'); -- assume the dbo schema by default