0

This question already has an answer here:

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?

marked as duplicate by LowlyDBA, RDFozz, Kris Gruttemeyer, Kin Shah sql-server Jul 23 '18 at 19:30

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

  • Truncating the table is similar to dropping and re-creating it. Even if you disable foreign keys that reference it, you still won't be able to truncate it. You will have to drop the foreign keys, truncate the table and re-create the foreign keys. 2 alternatives are using a DELETE instead of TRUNCATE (will take longer because of logging), or building the database from scrap with a schema script. – EzLo Jul 23 '18 at 7:01
  • Possible duplicate: dba.stackexchange.com/q/77018/1186 – Aaron Bertrand Jul 23 '18 at 12:10
0

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
  • Careful with foreign keys that have more than 1 column! – EzLo Jul 23 '18 at 9:22
0

Why don't you script out the entire database making sure to script out all procedures, views, functions,security, indexes, keys, unique constraints, and foreign keys etc...? You could script out the entire database without trying to find out the dependencies and hierarchy of the objects. You script out the entire database, drop the original, then run the script to recreate the database. See the section "Generate Scripts".

https://docs.microsoft.com/en-us/sql/ssms/tutorials/scripting-ssms?view=sql-server-2017

Not the answer you're looking for? Browse other questions tagged or ask your own question.