Stored procedure to truncate SQL Server tables in a specific database. Handles tables with foreign key dependencies.

TRUNCATE_TABLES.sql

-- Choose the database where to store and use the TRUNCATE_TABLES procedure.
USE [YDTH] -- YOUR_DATABASE_TO_HANDLE
GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TRUNCATE_TABLES]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[TRUNCATE_TABLES] AS'
END
GO

-- Heavily inspired by GhotiPhud's code at https://stackoverflow.com/a/54353773/2332350
-- Note that the collation is set to French_100_CI_AS_SC_UTF8 and can be changed in the code below.
-- The #scripts and #FK temporary tables could also be replaced with ##scripts_YDTH and ##FK_YDTH
-- global temporary tables to avoid concurrent calls to TRUNCATE_TABLES.
ALTER PROCEDURE TRUNCATE_TABLES (
       @table_name   NVARCHAR(MAX) = N''    -- blank means all tables, otherwise specify the table name
     , @table_schema NVARCHAR(MAX) = N'dbo' -- blank means all schemas, otherwise specify the schema name
     , @do_exec      BIT           = 0      -- 0 means debug mode (no truncations), 1 for real execution
     ) AS
BEGIN
    SET XACT_ABORT ON;
    BEGIN TRANSACTION;

    WITH foreignKeys
    AS (
        SELECT SCHEMA_NAME(fk.schema_id) AS SchemaName
            , fk.Name AS ConstraintName
            , OBJECT_NAME(fk.parent_object_id) AS TableName
            , SCHEMA_NAME(t.SCHEMA_ID) AS ReferencedSchemaName
            , OBJECT_NAME(fk.referenced_object_id) AS ReferencedTableName
            , fc.constraint_column_id
            , COL_NAME(fk.parent_object_id, fc.parent_column_id) AS ColumnName
            , COL_NAME(fk.referenced_object_id, fc.referenced_column_id) AS ReferencedColumnName
            , fk.delete_referential_action_desc
            , fk.update_referential_action_desc
        FROM sys.foreign_keys AS fk
        INNER JOIN sys.foreign_key_columns AS fc ON fk.object_id = fc.constraint_object_id
        INNER JOIN sys.tables t ON OBJECT_NAME(t.object_id) = IIF(@table_name = N'', OBJECT_NAME(t.object_id), @table_name)
            AND SCHEMA_NAME(t.schema_id) = IIF(@table_schema = N'', SCHEMA_NAME(t.schema_id), @table_schema)
            AND t.OBJECT_ID = fc.referenced_object_id
            AND OBJECT_NAME(t.object_id) <> N'sysdiagrams'
        )
    SELECT quotename(fk.ConstraintName) AS ConstraintName
        , quotename(fk.SchemaName) + N'.' + quotename(fk.TableName) AS TableName
        , quotename(fk.ReferencedSchemaName) + N'.' + quotename(fk.ReferencedTableName) AS ReferencedTableName
        , replace(fk.delete_referential_action_desc, N'_', N' ') AS DeleteAction
        , replace(fk.update_referential_action_desc, N'_', N' ') AS UpdateAction
        , STUFF((
                SELECT N',' + quotename(fk2.ColumnName)
                FROM foreignKeys fk2
                WHERE fk2.ConstraintName = fk.ConstraintName
                    AND fk2.SchemaName = fk.SchemaName
                ORDER BY fk2.constraint_column_id
                FOR XML PATH(N'')
                ), 1, 1, N'') AS ColumnNames
        , STUFF((
                SELECT N',' + quotename(fk2.ReferencedColumnName)
                FROM foreignKeys fk2
                WHERE fk2.ConstraintName = fk.ConstraintName
                    AND fk2.SchemaName = fk.SchemaName
                ORDER BY fk2.constraint_column_id
                FOR XML PATH(N'')
                ), 1, 1, N'') AS ReferencedColumnNames
    INTO #FKs
    FROM foreignKeys fk
    GROUP BY fk.SchemaName
        , fk.ConstraintName
        , fk.TableName
        , fk.ReferencedSchemaName
        , fk.ReferencedTableName
        , fk.delete_referential_action_desc
        , fk.update_referential_action_desc;

    -- Drop FKs
    SELECT identity(INT, 1, 1) AS ID
        , N'ALTER TABLE ' + fk.TableName + N' DROP CONSTRAINT ' + fk.ConstraintName + N';' COLLATE French_100_CI_AS_SC_UTF8 AS script
    INTO #scripts
    FROM #FKs fk;

    -- Truncate 
    INSERT INTO #scripts
    SELECT DISTINCT N'TRUNCATE TABLE ' + fk.TableName + N';' COLLATE French_100_CI_AS_SC_UTF8 AS script
    FROM #FKs fk;

    -- Truncate 
    INSERT INTO #scripts
    SELECT DISTINCT N'TRUNCATE TABLE ' + quotename(SCHEMA_NAME(t.schema_id)) + N'.' + quotename(OBJECT_NAME(t.object_id)) + N';' COLLATE French_100_CI_AS_SC_UTF8 AS script
    FROM sys.tables t
    WHERE OBJECT_NAME(t.object_id) = IIF(@table_name = N'', OBJECT_NAME(t.object_id), @table_name)
        AND SCHEMA_NAME(t.schema_id) = IIF(@table_schema = N'', SCHEMA_NAME(t.schema_id), @table_schema)
        AND OBJECT_NAME(t.object_id) <> N'sysdiagrams'
        AND NOT EXISTS (
            SELECT 1
            FROM #FKs fk
            WHERE fk.TableName = quotename(SCHEMA_NAME(t.schema_id)) + N'.' + quotename(OBJECT_NAME(t.object_id))
            );

    -- Recreate
    INSERT INTO #scripts
    SELECT N'ALTER TABLE ' + fk.TableName + N' WITH CHECK ADD CONSTRAINT ' + fk.ConstraintName + N' FOREIGN KEY (' + fk.ColumnNames + N')' + N' REFERENCES ' + fk.ReferencedTableName + N' (' + fk.ReferencedColumnNames + N')' + N' ON DELETE ' + fk.DeleteAction COLLATE French_100_CI_AS_SC_UTF8 + N' ON UPDATE ' + fk.UpdateAction + N';' COLLATE French_100_CI_AS_SC_UTF8 AS script
    FROM #FKs fk;

    DECLARE @script NVARCHAR(MAX);

    DECLARE curScripts CURSOR
    FOR
    SELECT script
    FROM #scripts
    ORDER BY ID;

    OPEN curScripts;

    WHILE 1 = 1
    BEGIN
        FETCH NEXT
        FROM curScripts
        INTO @script;

        IF @@FETCH_STATUS != 0
            BREAK;

        PRINT @script;

        IF @do_exec = 1
            EXEC (@script);
    END

    CLOSE curScripts;

    DEALLOCATE curScripts;

    DROP TABLE #scripts;

    DROP TABLE #FKs;

    COMMIT;
END;

GO