Drop all constraints from tables with a certain prefix-CodePudding

I'm trying to delete certain tables from my database that have a certain prefix using SQL Server 2014, these tables have constraints of course, so I looked for the equivalent of SET foreign_key_checks = 0 in MySQL and so far i tried this :

Attempt

Running

EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'

then drop all tables with this generated script :

SELECT 'drop table '   table_name
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE 'myprefix%'

then

EXEC sp_msforeachtable 'ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL'

Result

Some tables were deleted, and some threw an error

... is referenced by a FOREIGN KEY constraint.

which is weird because I thought that the stored procedure I ran disabled them.

Is there a way to delete these constraints all-together? Since I have the scripts to create them again, is there a way I can delete the constraints from sys.foreign_keys but only from the tables that have the prefix?

Edit - Context

I was working in isolation on an existing module that's part of our huge web app, now it's time to include the changes I made and sync them with the dev database, I was asked to generate the scripts to update it for all the tables, views, stored procedures.. etc that I created.

So, instead of doing scripts that add columns to existing tables, I though it'd be much easier to just drop the tables, and then re-create them, data is not important here, it's all dummy data I used while testing.

CodePudding user response：

Since you mentioned already having the scripts required to regenerate the foreign keys, here's a way to generate and execute the drops. You might want to add some error handling.


declare @sql varchar(max);

declare c cursor local fast_forward for 
   select   concat
            (
               'alter table ',
               quotename(s.name), 
               '.', 
               quotename(t.name),
               ' drop constraint ',
               quotename(fk.name)
            )
   from     sys.foreign_keys  fk
   join     sys.tables        t  on t.object_id = fk.parent_object_id
   join     sys.schemas       s  on t.schema_id = s.schema_id
   where    t.is_ms_shipped = 0
            and t.name like 'myprefix%';

open c;
fetch next from c into @sql;
while (@@fetch_status = 0) 
begin   
   print @sql;
   --exec(@sql); uncomment after checking output and run again (or just run output manually)
   fetch next from c into @sql;
end