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

You simply need to wrap your "delete from all the tables" script with a "drop all foreign keys" script at the beginning, and "re-create all foreign keys" script at the end. I show one way to do that here:

• Drop and Re-Create All Foreign Key Constraints in SQL Server

However, I would argue that it is much cleaner to just script out the database and empty objects from source control than spend all this time and effort deleting data from one table at a time.

Anyway an attempt at what you're doing (if you truncate you don't also need to checkident, I'm not sure I would ever use undocumented and unsupported procedures like sp_msforeachtable, and I also stay the heck away from INFORMATION_SCHEMA). Please try this on a test database.

CREATE TABLE #x -- feel free to use a permanent table
(
  drop_script   nvarchar(max),
  create_script nvarchar(max)
);
  
DECLARE @drop   nvarchar(max) = N'',
        @create nvarchar(max) = N'';

-- drop is easy, just build a simple concatenated list from sys.foreign_keys:
SELECT @drop += N'
ALTER TABLE ' + QUOTENAME(cs.name) + '.' + QUOTENAME(ct.name) 
    + ' DROP CONSTRAINT ' + QUOTENAME(fk.name) + ';'
FROM sys.foreign_keys AS fk
INNER JOIN sys.tables AS ct
  ON fk.parent_object_id = ct.[object_id]
INNER JOIN sys.schemas AS cs 
  ON ct.[schema_id] = cs.[schema_id];

INSERT #x(drop_script) SELECT @drop;

-- create is a little more complex. We need to generate the list of 
-- columns on both sides of the constraint, even though in most cases
-- there is only one column.
SELECT @create += 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'./text()[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'./text()[1]', N'nvarchar(max)'), 1, 1, N'') + ');'
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;

UPDATE #x SET create_script = @create;

PRINT @drop;
PRINT @create;

EXEC sys.sp_executesql @drop
-- clear out data etc. here

DECLARE @truncate nvarchar(max) = N'';
SELECT @truncate += N'TRUNCATE TABLE ' + QUOTENAME(s.name)
  + N'.' + QUOTENAME(t.name) + N';'
FROM sys.schemas AS s 
  INNER JOIN sys.tables AS t
  ON s.[schema_id] = t.[schema_id];

EXEC sys.sp_executesql @truncate;
EXEC sys.sp_executesql @create;

Notes:

• this is untested. As ludly as I can: try this on a test database.
• this was meant to execute exactly once, so I don't drop the #temp table (it may be useful to keep it alive long enough to troubleshoot if things go south)
• PRINT is not necessarily going to show you the full command that is going to be executed, so it's not a valid way to determine if the script is correct. It is just meant as a quick eyeball. If you really want to view the whole command, you'll need something a little more elaborate.
• this doesn't handle indexed views, and I'm sure there are other limitations that might prevent you from truncating some tables (I'm thinking temporal or always encrypted with enclaves or in-mem), but I would resolve those separately and keep truncate around instead of "fixing" that by using a much more log-intensive delete.

Answer 2

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