If you are trying to truncate some tables and are getting the error: Cannot truncate table 'YourTableName' because it is being referenced by a FOREIGN KEY constraint it is because both TRUNCATE TABLE and DROP TABLE commands are not allowed on tables being referenced by foreign key constraints. Other restrictions preventing truncate include:

  1. Tables referenced by an indexed view
  2. Published in transactional or merge replication
  3. Temporal tables
  4. Referenced by an EDGE constraint
  5. Enabled for Change Data Capture (something Microsoft forgot to mention as a restriction on their list)

Before truncating or dropping a table it is necessary to drop these FK constraints and - in case you want to preserve referential integrity - recreate them.

Cannot truncate table

The scripts below allows you to safely drop and recreate all FK constraints for schemas/tables specified by the schema/table lists (parameters @ListOfSchemaNames and @ListOfTableNames) or - if you wish - per entire database by setting the parameter @DropAllFKPerDB = 1.

To read more about TRUNCATE TABLE command please refer to the Microsoft article or to an excellent explanation of how truncate works by Paul Randal.

The solution consists of 2 scripts which have to be run in the context of the same database but in separate sessions (why - explained in point 2.)

  1. Prepares and saves the drop/create constraint commands into a table [_ForeignKeyConstraintDefinitions], based on the delimited list of tables/schemas specified in parameters @ListOfSchemaNames and @ListOfTableNames (if you have same table names in more than one schema and you specify these schemas in the @ListOfSchemaNames the script will prepare the drop/recreate commands for all tables)
  2. Prints out or executes the commands depending on the option chosen: you can choose either @Drop or @Recreate and setting @Execute = 1 will actually run the prepared commands and confirm each execution with - Executed Successfully annotation. The 2nd part needs to be run in a separate session to avoid accidentally overwriting your [_ForeignKeyConstraintDefinitions] table with empty values after dropping all constraints.

If you want to ignore the @ListOfSchemaNames/@ListOfTableNames and generate drop/recreate commands for ALL FK constraints within the entire DB set the parameter: @DropAllFKPerDB = 1. Here is the First Part::

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
USE [YourDbName]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
SET NOCOUNT ON
GO

/* ==================================================================================================================== */
/* Author:      CleanSql.com ©                                                                                          */
/* Create date: 2022-04-28                                                                                              */
/* Description: Collects list of ForeignKeys referencing the tables specified in @ListOfSchemaNames/@ListOfTableNames   */
/* ==================================================================================================================== */
/* Change History:                                                                                                      */
/* -------------------------------------------------------------------------------------------------------------------- */
/* Date:       User:           Version:  Change:                                                                        */
/* -------------------------------------------------------------------------------------------------------------------- */
/* 2022-04-28  CleanSql.com    1.0       Created                                                                        */
/* -------------------------------------------------------------------------------------------------------------------- */
/* ==================================================================================================================== */

DECLARE
      @ListOfSchemaNames NVARCHAR(4000)
    , @ListOfTableNames NVARCHAR(4000)
    , @DropAllFKsPerDB BIT
    , @delim CHAR(1)

    , @ObjectID INT
    , @ErrorMsg NVARCHAR(2047)

    , @schema_id NVARCHAR(4000)
    , @object_id NVARCHAR(4000)    

    , @start_search_sch INT
    , @delim_pos_sch INT
    , @schema_name SYSNAME

    , @start_search_tbl INT
    , @delim_pos_tbl INT
    , @name_tbl SYSNAME
    , @SqlEngineVersion INT

/* Set the list of Schemas and Tables you want to truncate; use a list of schemas and tables separated and terminated by the @delim charachter */
/* Below a sample list of tables and schemas from AdventureWorks2019 - fill in your values as you please */

SET @ListOfSchemaNames = 'Production;HumanResources;Person;'
SET @ListOfTableNames  = 'Product;Employee;BusinessEntity;'
SET @delim = ';' /* character used to delimit and terminate the items in the lists above */
SET @DropAllFKsPerDB = 0 /* Set @DropAllFKsPerDB to = 1 ONLY if you want to ignore the @ListOfSchemaNames/@ListOfTableNames above
                            and generate drop/re-create commands for ALL FK constraints within the ENTIRE DB */

/* After the script executes CLOSE OR DISCONNECT THIS SESSION!!! - your commands are prepared in the table [_ForeignKeyConstraintDefinitions]
   and you do not want to overwrite this table by re-running this script accidentally
   Do not delete the [_ForeignKeyConstraintDefinitions] table untill you are 100% sure that you no longer need it */

DROP TABLE IF EXISTS [#SelectedObjectList];
CREATE TABLE [#SelectedObjectList]
(
	 [Id]                 INT PRIMARY KEY CLUSTERED IDENTITY(1,1)
	,[SchemaID]           INT NOT NULL
	,[SchemaName]         SYSNAME NOT NULL
	,[ObjectID]           INT NOT NULL
	,[TableName]          SYSNAME NOT NULL
)

DROP TABLE IF EXISTS [dbo].[_ForeignKeyConstraintDefinitions]
CREATE TABLE [dbo].[_ForeignKeyConstraintDefinitions]

(
    [LineId]                          INT PRIMARY KEY CLUSTERED IDENTITY(1,1)
   ,[Foreign_Key_Id]                  INT UNIQUE NOT NULL
   ,[Drop_Constraint_Command]         NVARCHAR(MAX) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
   ,[Recreate_Constraint_Command]     NVARCHAR(MAX) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)


IF (@DropAllFKsPerDB <> 1) AND ((RIGHT(@ListOfSchemaNames, 1) <> @delim) OR (RIGHT(@ListOfTableNames, 1) <> @delim))
BEGIN
SET @ErrorMsg
    = N'Strings: @ListOfSchemaNames and @ListOfTableNames have to end with the delimiter scpecified in @delim variable: [' + @delim + ']';
RAISERROR(@ErrorMsg, 16, 1);
RETURN;
END

/* ----------------------------------- Search through @ListOfSchemaNames: ----------------------------------- */
SET @start_search_sch = 0
SET @delim_pos_sch = 0
IF (@DropAllFKsPerDB <> 1)
BEGIN
    WHILE CHARINDEX(@delim, @ListOfSchemaNames, @start_search_sch + 1) > 0
    BEGIN
        SET @delim_pos_sch = CHARINDEX(@delim, @ListOfSchemaNames, @start_search_sch + 1) - @start_search_sch
        SET @schema_name = SUBSTRING(@ListOfSchemaNames, @start_search_sch, @delim_pos_sch)
        SET @schema_id = NULL;
        SELECT @schema_id = schema_id FROM sys.schemas WHERE name = @schema_name        
        IF (@schema_id IS NOT NULL)
           /* ----------------------------------- Search through @ListOfTableNames: ----------------------------------- */
           BEGIN
               SET @start_search_tbl = 0
               SET @delim_pos_tbl = 0

               WHILE CHARINDEX(@delim, @ListOfTableNames, @start_search_tbl + 1) > 0
               BEGIN
                   SET @delim_pos_tbl = CHARINDEX(@delim, @ListOfTableNames, @start_search_tbl + 1) - @start_search_tbl
                   SET @name_tbl = SUBSTRING(@ListOfTableNames, @start_search_tbl, @delim_pos_tbl)
                   SET @object_id = NULL;
                   SELECT @object_id = object_id FROM sys.objects WHERE schema_id = @schema_id AND name = @name_tbl

                   SET @ObjectID = NULL;
                   SET @ObjectID = OBJECT_ID('[' + @schema_name + '].[' + @name_tbl + ']');
                   IF  (@object_id IS NOT NULL) AND (@ObjectID IS NOT NULL)
                   BEGIN
                       INSERT INTO [#SelectedObjectList] (
                                   [SchemaID]
                                  ,[SchemaName]             
                                  ,[ObjectID]
                                  ,[TableName]             
                       )
                       VALUES (
                                   @schema_id
                                  ,@schema_name
                                  ,@object_id
                                  ,@name_tbl
                       )
                   END
                   SET @start_search_tbl = CHARINDEX(@delim, @ListOfTableNames, @start_search_tbl + @delim_pos_tbl) + 1
               END
           END
           /* ----------------------------------- End of Seaching through @ListOfTableNames -------------------------------- */
        SET @start_search_sch = CHARINDEX(@delim, @ListOfSchemaNames, @start_search_sch + @delim_pos_sch) + 1
    END
END
ELSE
BEGIN
        INSERT INTO [#SelectedObjectList] (
                    [SchemaID]
                   ,[SchemaName]             
                   ,[ObjectID]
                   ,[TableName]             
        )
        SELECT  SCHEMA_ID(TABLE_SCHEMA), TABLE_SCHEMA, OBJECT_ID(QUOTENAME(TABLE_SCHEMA)+'.'+QUOTENAME(TABLE_NAME)), TABLE_NAME
        FROM    INFORMATION_SCHEMA.TABLES
        WHERE   TABLE_TYPE = 'BASE TABLE'
END
/* ----------------------------------- End of Seaching through @ListOfSchemaNames -------------------------------- */
--SELECT * FROM [#SelectedObjectList]

IF  (@DropAllFKsPerDB <> 1) AND (SELECT COUNT(*) FROM [#SelectedObjectList]) < 1
BEGIN
    BEGIN
    SET @ErrorMsg
        = N'Could not find any objects specified in the list of schemas: [' + @ListOfSchemaNames + N'] and tables: [' + @ListOfTableNames + N'] in the database: [' + DB_NAME(DB_ID()) + N'].';
    RAISERROR(@ErrorMsg, 16, 1);
    RETURN;
    END
END

SELECT @SqlEngineVersion = CAST(SUBSTRING(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(20)), 1, 2) AS INT)
; WITH [cte] AS (
SELECT          
                fk.object_id                                                                                    AS [Foreign_Key_Id]
               ,fk.name                                                                                         AS [Foreign_Key_Name]
               ,[sch_src].[SchemaName]                                                                          AS [Schema_Name_Src]
               ,(SELECT (OBJECT_NAME([fkc].parent_object_id)))                                                  AS [Table_Name_Src]
               ,[fkc].parent_column_id                                                                          AS [Column_Id_Src]
               ,[col_src].name                                                                                  AS [Column_Name_Src]
               ,[sch_tgt].[SchemaName]                                                                          AS [Schema_Name_Trgt]                      
               ,(SELECT (OBJECT_NAME([fkc].referenced_object_id)))                                              AS [Table_Name_Trgt]
               ,[fkc].referenced_column_id                                                                      AS [Column_Id_Trgt]
               ,[col_tgt].name                                                                                  AS [Column_Name_Trgt]
               ,[sch_tgt].[SchemaId]                                                                            AS [Schema_Id_Trgt]
               ,[fk].delete_referential_action
               ,[fk].update_referential_action
               ,OBJECT_ID('[' + [sch_tgt].[SchemaName] + '].[' + OBJECT_NAME([fkc].referenced_object_id) + ']') AS [Object_Id_Trgt]
FROM            sys.foreign_keys                                               AS [fk]
CROSS APPLY     (
                    SELECT  
                            [fkc].parent_column_id,
                            [fkc].parent_object_id,
                            [fkc].referenced_object_id,
                            [fkc].referenced_column_id
                    FROM    sys.foreign_key_columns                            AS [fkc]
                    WHERE   1 = 1
                    AND     fk.parent_object_id = [fkc].parent_object_id
                    AND     fk.referenced_object_id = [fkc].referenced_object_id
                    AND     fk.object_id = [fkc].constraint_object_id
                )                                                              AS [fkc]
CROSS APPLY     (
                    SELECT     [ss].name                                       AS [SchemaName]
                    FROM       sys.objects                                     AS [so]
                    INNER JOIN sys.schemas                                     AS [ss] ON [ss].schema_id = [so].schema_id
                    WHERE      [so].object_id = [fkc].parent_object_id
                )                                                              AS [sch_src]
CROSS APPLY     (
                    SELECT [sc].name      
                    FROM   sys.columns                                         AS [sc]
                    WHERE  [sc].object_id = fk.[parent_object_id]
                    AND    [sc].column_id = [fkc].[parent_column_id]
                )                                                              AS [col_src]
CROSS APPLY     (
                    SELECT     [ss].schema_id                                  AS [SchemaId]
                              ,[ss].name                                       AS [SchemaName]
                    FROM       sys.objects                                     AS [so]
                    INNER JOIN sys.schemas                                     AS [ss] ON [ss].schema_id = [so].schema_id
                    WHERE      [so].object_id = [fkc].referenced_object_id
                )                                                              AS [sch_tgt]
CROSS APPLY     (
                    SELECT [sc].name      
                    FROM   sys.columns                                         AS [sc]
                    WHERE  [sc].object_id = fk.[referenced_object_id]
                    AND    [sc].column_id = [fkc].[referenced_column_id]
                )                                                              AS [col_tgt]
INNER JOIN      [#SelectedObjectList]                                          AS [sol]
ON              [sol].SchemaID = [sch_tgt].[SchemaId]
/* if you want to search by source schema+table names (rather than target) uncomment line below and comment the next one */
--AND             [sol].[ObjectID] = OBJECT_ID('[' + [sch_src].[SchemaName] + '].[' + OBJECT_NAME([fkc].parent_object_id) + ']')
AND             [sol].[ObjectID] = OBJECT_ID('[' + [sch_tgt].[SchemaName] + '].[' + OBJECT_NAME([fkc].referenced_object_id) + ']')

/* ORDER BY        fk.object_id, Schema_Name_Trgt, Table_Name_Trgt */
)
INSERT INTO  [dbo].[_ForeignKeyConstraintDefinitions](
             [Foreign_Key_Id]              
            ,[Drop_Constraint_Command]     
            ,[Recreate_Constraint_Command]
)
SELECT         
             [cte].[Foreign_Key_Id],
             [Drop_Constraint_Command] =
                    'ALTER TABLE ' + QUOTENAME([cte].[Schema_Name_Src]) + '.' + QUOTENAME([cte].[Table_Name_Src])+' DROP CONSTRAINT ' + QUOTENAME([cte].[Foreign_Key_Name]) + ';',        

             [Recreate_Constraint_Command] =
             CONCAT('ALTER TABLE ' + QUOTENAME([cte].[Schema_Name_Src]) + '.'+ QUOTENAME([cte].[Table_Name_Src])+' WITH NOCHECK ADD CONSTRAINT ' + QUOTENAME([cte].[Foreign_Key_Name]) + ' ',
             CASE
             WHEN @SqlEngineVersion < 14
                    /* For SQL Versions older than 14 (2017) use FOR XML PATH for all multi-column constraints: */
             THEN   'FOREIGN KEY ('+ STUFF((SELECT   ', ' + QUOTENAME([t].[Column_Name_Src])
                                            FROM      [cte] AS [t]
                                            WHERE     [t].Foreign_Key_Id = [cte].[Foreign_Key_Id]
                                            ORDER BY  [t].Column_Id_Trgt --This is identical to the ORDER BY in WITHIN GROUP clause in STRING_AGG
                                            FOR XML PATH(''),TYPE).value('(./text())[1]', 'VARCHAR(MAX)'), 1, 2,'') + ' ) ' +
                    'REFERENCES '  + QUOTENAME([cte].[Schema_Name_Trgt])+'.'+ QUOTENAME([cte].[Table_Name_Trgt])+
                              ' (' + STUFF((SELECT   ', ' + QUOTENAME([t].[Column_Name_Trgt])
                                            FROM      [cte] AS [t]
                                            WHERE     [t].[Foreign_Key_Id] = [cte].[Foreign_Key_Id]
                                            ORDER BY  [t].[Column_Id_Trgt] --This is identical to the ORDER BY in WITHIN GROUP clause in STRING_AGG
                                            FOR XML PATH(''),TYPE).value('(./text())[1]', 'VARCHAR(MAX)'), 1, 2,'') + ' )'                 
             ELSE   
                    /* For SQL Versions 2017+ use STRING_AGG for all multi-column constraints: */                  
                    'FOREIGN KEY ('+ STRING_AGG(QUOTENAME([cte].[Column_Name_Src]), ', ') WITHIN GROUP (ORDER BY [cte].[Column_Id_Trgt]) +') '+
                    'REFERENCES '  + QUOTENAME([cte].[Schema_Name_Trgt])+'.'+ QUOTENAME([cte].[Table_Name_Trgt])+' ('+ STRING_AGG(QUOTENAME([cte].[Column_Name_Trgt]), ', ') + ')'             
             END,   
             CASE
                 WHEN [cte].delete_referential_action = 1 THEN ' ON DELETE CASCADE '
                 WHEN [cte].delete_referential_action = 2 THEN ' ON DELETE SET NULL '
                 WHEN [cte].delete_referential_action = 3 THEN ' ON DELETE SET DEFAULT '
                 ELSE ''
             END,
             CASE
                 WHEN [cte].update_referential_action = 1 THEN ' ON UPDATE CASCADE '
                 WHEN [cte].update_referential_action = 2 THEN ' ON UPDATE SET NULL '
                 WHEN [cte].update_referential_action = 3 THEN ' ON UPDATE SET DEFAULT '
                 ELSE ''
             END,
             CHAR(13)+ 'ALTER TABLE ' + QUOTENAME([cte].[Schema_Name_Src])+'.'+ QUOTENAME([cte].[Table_Name_Src])+' CHECK CONSTRAINT '+ QUOTENAME([cte].[Foreign_Key_Name])+';')
FROM         [cte]
GROUP BY     
             [cte].[Foreign_Key_Id]
            ,[cte].[Schema_Name_Src]
            ,[cte].[Table_Name_Src]
            ,[cte].[Foreign_Key_Name]
            ,[cte].[Schema_Name_Trgt]
            ,[cte].[Table_Name_Trgt]
            ,[cte].delete_referential_action
            ,[cte].update_referential_action
ORDER BY     [cte].[Table_Name_Src]

IF  (SELECT COUNT(*) FROM [dbo].[_ForeignKeyConstraintDefinitions]) < 1
BEGIN
    BEGIN
    SET @ErrorMsg
        = N'Could not find any foreign keys referencing the tables specified in the list of schemas: [' + @ListOfSchemaNames + N'] and tables: [' + @ListOfTableNames + N'] in the database: [' + DB_NAME(DB_ID()) + N'].';
    RAISERROR(@ErrorMsg, 16, 1);
    RETURN;
    END
END

SELECT
            [Foreign_Key_Id]              
           ,[Drop_Constraint_Command]     
           ,[Recreate_Constraint_Command]
FROM        [dbo].[_ForeignKeyConstraintDefinitions]
ORDER BY    [Recreate_Constraint_Command], [Foreign_Key_Id]

The Second Part needs to be executed IN A SEPARATE SESSION from first script to avoid accidentally overwriting your [_ForeignKeyConstraintDefinitions] table with empty values after dropping all constraints:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
--USE [YourDbName]
--GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
SET NOCOUNT ON
GO

/* ==================================================================================================================== */
/* Author:      CleanSql.com ©                                                                                          */
/* Create date: 2022-04-28                                                                                              */
/* Description: Drops ForeignKeys collcted in [dbo].[_ForeignKeyConstraintDefinitions]                                  */
/* ==================================================================================================================== */
/* Change History:                                                                                                      */
/* -------------------------------------------------------------------------------------------------------------------- */
/* Date:       User:           Version:  Change:                                                                        */
/* -------------------------------------------------------------------------------------------------------------------- */
/* 2022-04-28  CleanSql.com    1.0       Created                                                                        */
/* -------------------------------------------------------------------------------------------------------------------- */
/* ==================================================================================================================== */

/* ==================================================================================================================== */
/* !!! ATTENTION - from this line to the end: run this code-section IN A SEPARATE SESSION !!! from first script
   to avoid accidentally overwriting your [_ForeignKeyConstraintDefinitions] table with empty values
   after dropping all constraints */
DECLARE
             @Foreign_Key_Id              INT
            ,@Drop_Constraint_Command     NVARCHAR(MAX)
            ,@Recreate_Constraint_Command NVARCHAR(MAX)
            ,@Command                     NVARCHAR(MAX)
            ,@Drop                        BIT
            ,@Recreate                    BIT
            ,@Execute                     BIT
            ,@RowCount                    INT
            ,@LineId                      INT = 1   

SET @Drop = 1
SET @Recreate = 0
/* CAUTION!!!! SETTING @Execute = 1 WILL EXECUTE ALL @Drop OR @Recreate COMMANDS: */
SET @Execute = 0 /* 0 = Print out the @Command only */

SET XACT_ABORT ON
IF (@Execute = 0)
BEGIN
     PRINT('-----------------------------------------------------------------------------------------');
     PRINT('Below is the PRINTOUT ONLY of the commands to be executed once the @Execute is set to = 1');
     PRINT('-----------------------------------------------------------------------------------------');
END

SELECT @RowCount = COUNT(LineId) FROM [dbo].[_ForeignKeyConstraintDefinitions]
WHILE @LineId <= @RowCount
      BEGIN
            SELECT      
                         @Foreign_Key_Id                = [Foreign_Key_Id]              
                        ,@Drop_Constraint_Command       = [Drop_Constraint_Command]     
                        ,@Recreate_Constraint_Command   = [Recreate_Constraint_Command]
            FROM         [dbo].[_ForeignKeyConstraintDefinitions]
            WHERE        LineId = @LineId

                  IF (@Drop = 1 AND @Recreate = 0)
                  BEGIN
                      SET @Command = @Drop_Constraint_Command
                  END
                  ELSE IF (@Drop = 0 AND @Recreate = 1)
                  BEGIN
                      SET @Command = @Recreate_Constraint_Command
                  END
                  ELSE
                  BEGIN
                        RAISERROR('Set one (and only one) of the parameters: @Drop or @Recreate = 1 so that either one of the actions is selected', 16, 1);
                        RETURN;
                  END
                  IF (@Execute = 0)
                  BEGIN                       
                       PRINT(@Command)
                  END
                  IF (@Execute = 1)
                  BEGIN
                      EXECUTE(@Command);
                      PRINT(CONCAT(@Command, ' - Executed Successfully'));
                  END
            SET @LineId = @LineId + 1;
      END

/* ======================================================================================================================= */
/* If you are absolutely sure that you no longer need the [dbo].[_ForeignKeyConstraintDefinitions]  table then drop it:
DROP TABLE [dbo].[_ForeignKeyConstraintDefinitions]
*/

If you successfully drop the constraints then you’ll be able to run truncate command however, be careful: truncating the target table means that you may not be able to recreate the constraint(s) if the source table contains data in the column that is referencing your (already truncated) target.

For example if your BusinessEntity table (target) is being referenced by a FK in BusinessEntityAddress (source) like this:

BusinessEntity before truncate

then sure, you could drop that FK reference in order to truncate the target (BusinessEntity), but you can’t recreate that FK relation once BusinessEntity is empty and BusinessEntityAddress contains entries (in BusinessEntityID column) pointing to it:

BusinessEntity after truncate

In such scenario it is better to save the data of the target into a temp table before running the truncate. If you are absolutely sure about the safety of your data here is the 3rd script that will truncate your table(s) (assuming there are no other restrictions preventing truncate, as listed at the beginning) based on either:

  1. a delimiter-separated list of schemas/tables you specify or
  2. a sledge-hammer option: truncate ALL your tables within the entire db by setting the parameter @TruncateAllTablesPerDB = 1 (use at your own risk):
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
--USE [YourDbName]
--GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
SET NOCOUNT ON
GO

/* ==================================================================================================================== */
/* Author:      CleanSql.com ©                                                                                          */
/* Create date: 2022-04-28                                                                                              */
/* Description: Truncates tables specified in @ListOfSchemaNames/@ListOfTableNames assuming that FK-drop script above   */
/*              completed successfully                                                                                  */
/* ==================================================================================================================== */
/* Change History:                                                                                                      */
/* -------------------------------------------------------------------------------------------------------------------- */
/* Date:       User:           Version:  Change:                                                                        */
/* -------------------------------------------------------------------------------------------------------------------- */
/* 2022-04-28  CleanSql.com    1.0       Created                                                                        */
/* -------------------------------------------------------------------------------------------------------------------- */
/* ==================================================================================================================== */

DECLARE
      @ListOfSchemaNames NVARCHAR(4000)
    , @ListOfTableNames NVARCHAR(4000)
    , @TruncateAllTablesPerDB BIT
	, @delim CHAR(1)

    , @ObjectID INT
    , @ErrorMsg NVARCHAR(2047)

    , @schema_id NVARCHAR(4000)
    , @object_id NVARCHAR(4000)    

    , @start_search_sch INT
	, @delim_pos_sch INT
	, @schema_name SYSNAME

    , @start_search_tbl INT
	, @delim_pos_tbl INT
	, @name_tbl SYSNAME

    , @Command  NVARCHAR(MAX)
    , @Execute  BIT
    , @RowCount INT
    , @LineId   INT = 1   

/* Set the list of schemas and Tables you want to truncate; use a list of schemas and tables separated and terminated by the @delim charachter */
/* Below a sample list of tables and schemas from AdventureWorks2019 - fill in your values as you please */

SET @ListOfSchemaNames = 'Production;HumanResources;Person;'
SET @ListOfTableNames  = 'Product;Employee;BusinessEntity;'
SET @delim = ';' /* character used to delimit and terminate the items in the lists above */
SET @TruncateAllTablesPerDB = 0 /* Set @TruncateAllTablesPerDB to = 1 ONLY if you want to ignore the @ListOfSchemaNames/@ListOfTableNames above
                                   and generate TRUNCATE commands for all tables within the entire DB */
/* CAUTION!!!! SETTING @Execute = 1 WILL EXECUTE ALL @Drop OR @Recreate COMMANDS: */
SET @Execute = 0 /* 0 = Print out the @Command only */


DROP TABLE IF EXISTS [#SelectedObjectList];
CREATE TABLE [#SelectedObjectList]
(
	 [Id]                 INT PRIMARY KEY CLUSTERED IDENTITY(1,1)
	,[SchemaID]           INT NOT NULL
	,[SchemaName]         SYSNAME NOT NULL
	,[ObjectID]           INT NOT NULL
	,[TableName]          SYSNAME NOT NULL
)

IF (@TruncateAllTablesPerDB <> 1) AND ((RIGHT(@ListOfSchemaNames, 1) <> @delim) OR (RIGHT(@ListOfTableNames, 1) <> @delim))
BEGIN
SET @ErrorMsg
    = N'Strings: @ListOfSchemaNames and @ListOfTableNames have to end with the delimiter scpecified in @delim variable: [' + @delim + ']';
RAISERROR(@ErrorMsg, 16, 1);
RETURN;
END

/* ----------------------------------- Search through @ListOfSchemaNames: ----------------------------------- */
SET @start_search_sch = 0
SET @delim_pos_sch = 0
IF (@TruncateAllTablesPerDB <> 1)
BEGIN
    IF CHARINDEX(@ListOfTableNames, '_ForeignKeyConstraintDefinitions', 0) > 0
    BEGIN /* Remove the table _ForeignKeyConstraintDefinitions from the list just in case it got there (we do not want to truncate it: */
        SET @ListOfTableNames = REPLACE(@ListOfTableNames, '_ForeignKeyConstraintDefinitions', '')
    END
    WHILE CHARINDEX(@delim, @ListOfSchemaNames, @start_search_sch + 1) > 0
    BEGIN
        SET @delim_pos_sch = CHARINDEX(@delim, @ListOfSchemaNames, @start_search_sch + 1) - @start_search_sch
        SET @schema_name = SUBSTRING(@ListOfSchemaNames, @start_search_sch, @delim_pos_sch)
        SET @schema_id = NULL;
        SELECT @schema_id = [schema_id] FROM sys.schemas WHERE [name] = @schema_name        
        IF (@schema_id IS NOT NULL)
           /* ----------------------------------- Search through @ListOfTableNames: ----------------------------------- */
           BEGIN
               SET @start_search_tbl = 0
               SET @delim_pos_tbl = 0

               WHILE CHARINDEX(@delim, @ListOfTableNames, @start_search_tbl + 1) > 0
               BEGIN
                   SET @delim_pos_tbl = CHARINDEX(@delim, @ListOfTableNames, @start_search_tbl + 1) - @start_search_tbl
                   SET @name_tbl = SUBSTRING(@ListOfTableNames, @start_search_tbl, @delim_pos_tbl)
                   SET @object_id = NULL;
                   SELECT @object_id = object_id FROM sys.objects WHERE schema_id = @schema_id AND [name] = @name_tbl

                   SET @ObjectID = NULL;
                   SET @ObjectID = OBJECT_ID('[' + @schema_name + '].[' + @name_tbl + ']');
                   IF  (@object_id IS NOT NULL) AND (@ObjectID IS NOT NULL)
                   BEGIN
                       INSERT INTO [#SelectedObjectList] (
                                   [SchemaID]
                                  ,[SchemaName]             
                                  ,[ObjectID]
                                  ,[TableName]             
                       )
                       VALUES (
                                   @schema_id
                                  ,QUOTENAME(@schema_name)
                                  ,@object_id
                                  ,QUOTENAME(@name_tbl)
                       )
                   END
                   SET @start_search_tbl = CHARINDEX(@delim, @ListOfTableNames, @start_search_tbl + @delim_pos_tbl) + 1
               END
           END
           /* ----------------------------------- End of Seaching through @ListOfTableNames -------------------------------- */
        SET @start_search_sch = CHARINDEX(@delim, @ListOfSchemaNames, @start_search_sch + @delim_pos_sch) + 1
    END
END
ELSE
BEGIN
        INSERT INTO [#SelectedObjectList] (
                    [SchemaID]
                   ,[SchemaName]             
                   ,[ObjectID]
                   ,[TableName]             
        )
        SELECT  SCHEMA_ID(TABLE_SCHEMA), QUOTENAME(TABLE_SCHEMA), OBJECT_ID(QUOTENAME(TABLE_SCHEMA)+'.'+QUOTENAME(TABLE_NAME)), QUOTENAME(TABLE_NAME)
        FROM    INFORMATION_SCHEMA.TABLES
        WHERE   TABLE_TYPE = 'BASE TABLE'
        AND     TABLE_NAME <> '_ForeignKeyConstraintDefinitions';
END
/* ----------------------------------- End of Seaching through @ListOfSchemaNames -------------------------------- */

IF  (@TruncateAllTablesPerDB <> 1) AND (SELECT COUNT(*) FROM [#SelectedObjectList]) < 1
BEGIN
    BEGIN
    SET @ErrorMsg
        = N'Could not find any objects specified in the list of schemas: [' + @ListOfSchemaNames + N'] and tables: [' + @ListOfTableNames + N'] in the database: [' + DB_NAME(DB_ID()) + N'].';
    RAISERROR(@ErrorMsg, 16, 1);
    RETURN;
    END
END

SET XACT_ABORT ON
IF (@Execute = 0)
BEGIN
     PRINT('-----------------------------------------------------------------------------------------');
     PRINT('Below is the PRINTOUT ONLY of the commands to be executed once the @Execute is set to = 1');
     PRINT('-----------------------------------------------------------------------------------------');
END

SELECT @RowCount = COUNT(Id) FROM [#SelectedObjectList]
WHILE @LineId <= @RowCount
      BEGIN
            SELECT      
                         @Command = 'TRUNCATE TABLE '+[SchemaName]+'.'+[TableName]
            FROM         [#SelectedObjectList]
            WHERE        Id = @LineId

                  IF (@Execute = 0)
                  BEGIN                       
                       PRINT(@Command)
                  END
                  IF (@Execute = 1)
                  BEGIN
                      EXECUTE(@Command);
                      PRINT(CONCAT(@Command, ' - Executed Successfully'));
                  END

            SET @LineId = @LineId + 1;
      END;
GO