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?
Advertisement
Answer
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:
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.