Can't empty all my database in SQL Server In my SQL Server I tried to truncate all tables, but there have some constraint.

So I running this

EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all" -- Disable all constraints for database
EXEC sp_MSForEachTable 'TRUNCATE TABLE ?'


but still I get error as


  cant truncate table ... because it is being reference by a FK constraint


Why?
I've had a similar issue to this before, the issue is that you need to fully drop the FK constraints rather than disabling them. I used this code to convert non clustered primary keys to clustered ones but it'll work for this too (I've commented out the EXEC statements because somebody's going to mess up a system if I leave them in, try this as is first and then check the messages tab to see what it would actually do before running it in anger);

First query is going to put all of our Foreign Key information into a temporary table;

SELECT 
     o.name [ReferencedTable]
    ,OBJECT_NAME(fkc.parent_object_id) [FKTable]
    ,c.name [FKColumnName]
    ,fk.name [FKName] 
INTO #FKAgainstTableList
FROM sys.foreign_keys fk
INNER JOIN sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
INNER JOIN sys.columns c ON c.object_id = fk.parent_object_id 
    AND c.column_id = fkc.parent_column_id
INNER JOIN sys.objects o ON fkc.referenced_object_id = o.object_id


This is fine, but what if we've got foreign keys that reference more than one column? Let's allow for that by making a new table, it makes sense to put in our square brackets while we're doing this;

SELECT DISTINCT
     '[' + a.ReferencedTable + ']' ReferencedTable
    ,'[' + a.[FKTable] + ']' FKTableName
    ,STUFF([ColumnName],1,1,'') FKColumnName
    ,'[' + a.[FKName] + ']' FKName
INTO #SanitisedFKList
FROM #FKAgainstTableList a
CROSS APPLY
(
    SELECT ',[' + b.FKColumnName + ']'
    FROM #FKAgainstTableList b
    WHERE a.FKName = b.FKName
    FOR XML PATH ('')
) sub (ColumnName)


We'll need some variables for the cursors coming up;

DECLARE @sql varchar(max)
DECLARE @ReferencedTable varchar(max)
DECLARE @table varchar(max)
DECLARE @FKColumnName varchar(max)
DECLARE @FKName varchar(max)


Now let's make a cursor to drop all of these Foreign Keys (i like to keep the PRINT statements so I can see what's happening in the messages tab);

DECLARE cursor1 CURSOR  FOR
SELECT * FROM #SanitisedFKList

OPEN cursor1
FETCH NEXT FROM cursor1 INTO @ReferencedTable,@table,@FKColumnName,@FKName
WHILE   @@FETCH_STATUS = 0
    BEGIN
        SET @sql ='ALTER TABLE '+@table+' DROP CONSTRAINT '+ @FKName
        PRINT @sql
        --EXEC(@sql)
        FETCH NEXT FROM cursor1 INTO @ReferencedTable,@table,@FKColumnName,@FKName
    END
CLOSE cursor1
DEALLOCATE cursor1


Then you can do your original truncate table statements without worrying about those FK's.

When you want to reapply them you'll need that temp table and do something like this;

DECLARE cursor2 CURSOR  FOR
SELECT  * FROM #SanitisedFKList
OPEN cursor2
FETCH NEXT FROM cursor2 INTO @ReferencedTable,@table,@FKColumnName,@FKName
WHILE   @@FETCH_STATUS = 0
    BEGIN
        SET @sql = 'ALTER TABLE '+@table+' WITH NOCHECK ADD  CONSTRAINT  '+ @FKName+' FOREIGN KEY('+@FKColumnName+')
        REFERENCES '+@ReferencedTable+' ('+@FKColumnName+')'
        PRINT(@sql)
        --EXEC(@sql)
        SET @sql = 'ALTER TABLE '+@table+' CHECK CONSTRAINT  '+@FKName
        PRINT(@sql)
        --EXEC(@sql)

        FETCH NEXT FROM cursor2 INTO @ReferencedTable,@table,@FKColumnName,@FKName
    END
CLOSE cursor2
DEALLOCATE cursor2