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