T-SQL :: TRUNCATE or DELETE all tables in schema

Question

I need to TRUNCATE or DELETE all tables in schema.

I found this code:

-- disable all constraints
EXEC sp_MSForEachTable @command1='ALTER TABLE ? NOCHECK CONSTRAINT all',@whereand='and Schema_Id=Schema_id(''Person'')'

-- delete data in all tables
Exec Sp_msforeachtable @command1='Truncate Table ?',@whereand='and Schema_Id=Schema_id(''Person'')'

-- enable all constraints
exec sp_MSForEachTable @command1='ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all',@whereand='and Schema_Id=Schema_id(''Person'')'

-- if some of the tables have identity columns we may want to reseed them
EXEC sp_MSForEachTable @command1='DBCC CHECKIDENT ( ''?'', RESEED, 0)',@whereand='and Schema_Id=Schema_id(''Person'')'

but on AdventureWorks it gives me:

Cannot truncate table 'Person.Address' because it is being referenced by a FOREIGN KEY constraint.

So I found this alternative code:

DECLARE @STRSQL NVARCHAR(MAX);
DECLARE @TABLE NVARCHAR(128);
DECLARE @SCHEMA_NAME VARCHAR(50)

SET @SCHEMA_NAME = 'Person'
SET @STRSQL = '';

DECLARE @C1 CURSOR SET @C1 = CURSOR
FOR
SELECT TOP 2 TABLE_SCHEMA + '.' + TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = @SCHEMA_NAME

OPEN @C1

FETCH NEXT
FROM @C1
INTO @TABLE

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT @TABLE

    SET @STRSQL = @STRSQL + 'DELETE FROM ' + @TABLE + ';'

    FETCH NEXT
    FROM @C1
    INTO @TABLE
END

CLOSE @C1

DEALLOCATE @C1

PRINT @STRSQL

EXEC sp_executesql @STRSQL

But the result is the same:

Person.Address
Person.AddressType
DELETE FROM Person.Address;DELETE FROM Person.AddressType;
Msg 547, Level 16, State 0, Line 1
The DELETE statement conflicted with the REFERENCE constraint "FK_BusinessEntityAddress_Address_AddressID". The conflict occurred in database "AdventureWorks2014", table "Person.BusinessEntityAddress", column 'AddressID'.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 1
The DELETE statement conflicted with the REFERENCE constraint "FK_BusinessEntityAddress_AddressType_AddressTypeID". The conflict occurred in database "AdventureWorks2014", table "Person.BusinessEntityAddress", column 'AddressTypeID'.
The statement has been terminated.

How to TRUNCATE or DELETE all tables in schema?

Answer 1

Thank you @AaronBertrand, I forked your awesome query to make a more suitable one for what I need to do:

SELECT cs.name AS SchemaName
    ,ct.name AS TableName
    ,rt.name AS ColumnName
    ,fk.name AS ForeignKeyName
    ,fk.object_id AS ObjectID
    ,fk.parent_object_id AS ParentObjectID
    ,
    -- drop constraint 
    N'
ALTER TABLE ' + QUOTENAME(cs.name) + '.' + QUOTENAME(ct.name) + ' DROP CONSTRAINT ' + QUOTENAME(fk.name) + ';' AS Drop_Constraint_Script
    ,
    -- create constraint 
    N'
ALTER TABLE ' + QUOTENAME(cs.name) + '.' + QUOTENAME(ct.name) + ' ADD CONSTRAINT ' + QUOTENAME(fk.name) + ' FOREIGN KEY (' + STUFF((
            SELECT ',' + QUOTENAME(c.name)
            -- get all the columns in the constraint table
            FROM sys.columns AS c
            INNER JOIN sys.foreign_key_columns AS fkc ON fkc.parent_column_id = c.column_id
                AND fkc.parent_object_id = c.[object_id]
            WHERE fkc.constraint_object_id = fk.[object_id]
            ORDER BY fkc.constraint_column_id
            FOR XML PATH(N'')
                ,TYPE
            ).value(N'.[1]', N'nvarchar(max)'), 1, 1, N'') + ') REFERENCES ' + QUOTENAME(rs.name) + '.' + QUOTENAME(rt.name) + '(' + STUFF((
            SELECT ',' + QUOTENAME(c.name)
            -- get all the referenced columns
            FROM sys.columns AS c
            INNER JOIN sys.foreign_key_columns AS fkc ON fkc.referenced_column_id = c.column_id
                AND fkc.referenced_object_id = c.[object_id]
            WHERE fkc.constraint_object_id = fk.[object_id]
            ORDER BY fkc.constraint_column_id
            FOR XML PATH(N'')
                ,TYPE
            ).value(N'.[1]', N'nvarchar(max)'), 1, 1, N'') + ');' AS Create_Constraint_Script
FROM sys.foreign_keys AS fk
INNER JOIN sys.tables AS rt -- referenced table
    ON fk.referenced_object_id = rt.[object_id]
INNER JOIN sys.schemas AS rs ON rt.[schema_id] = rs.[schema_id]
INNER JOIN sys.tables AS ct -- constraint table
    ON fk.parent_object_id = ct.[object_id]
INNER JOIN sys.schemas AS cs ON ct.[schema_id] = cs.[schema_id]
WHERE rt.is_ms_shipped = 0
    AND ct.is_ms_shipped = 0
ORDER BY 1
    ,2
    ,3