T-SQL Script to Delete All The Relationships Between A Bunch Of Tables in a Schema and Other Bunch in another Schema?

Question

I have a set of tables (say Account, Customer) in a schema (say dbo) and I have some other tables (say Order, OrderItem) in another schema (say inventory). There's a relationship between the Order table and the Customer table. I want to delete all the relationships between the tables in the first schema (dbo) and the tables in the second schema (inventory), without deleting the relationships between tables inside the same schema.

Is that possible? Any help appreciated.

Solution 1

Use the metadata:

SELECT  *
FROM    INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE
WHERE   CONSTRAINT_NAME IN ( SELECT CONSTRAINT_NAME
                             FROM   INFORMATION_SCHEMA.TABLE_CONSTRAINTS
                             WHERE  CONSTRAINT_TYPE = 'FOREIGN KEY' )
SELECT  *
FROM    INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
WHERE   CONSTRAINT_NAME IN ( SELECT CONSTRAINT_NAME
                             FROM   INFORMATION_SCHEMA.TABLE_CONSTRAINTS
                             WHERE  CONSTRAINT_TYPE = 'FOREIGN KEY' )
SELECT  *
FROM    INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE   CONSTRAINT_TYPE = 'FOREIGN KEY'

Add filter criteria to find the constraints you want to DROP, then insert them in the template:

ALTER TABLE {TABLE_SCHEMA}.{TABLE_NAME} DROP {CONSTRAINT_NAME}

And execute with dynamic SQL.

Solution 2

Please use the script below by copying and pasting it to the SQL Studio:

SELECT  'ALTER TABLE ' + TABLE_SCHEMA + '.[' + TABLE_NAME + '] DROP [' + CONSTRAINT_NAME + ']'
FROM    INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE   CONSTRAINT_TYPE = 'FOREIGN KEY'

Solution 3

you can get the available FKs that exist in your db by below script and then delete them :

select * from sys.objects o
join sys.schemas s on o.schema_id = s.schema_id
where o.type = 'F'

after that delete like below

ALTER TABLE {TABLE_SCHEMA}.{TABLE_NAME} DROP {CONSTRAINT_NAME}

Solution 4

I won't swear this will work on SQL 2005, as I don't have an instance to test it with, but if it does it will make it a simple copy/paste job in SSMS. I'll leave it to you to iterate the results and execute if that's what you want.

Replace schema_1 and schema_2 with the schema names you're trying to find relationships between.

declare @s1 int
declare @s2 int

set @s1 = schema_id( 'schema_1' )
set @s2 = schema_id( 'schema_2' )

select
     N'alter table [' + s.name + N'].[' + o_p.name + N'] drop constraint [' + fk.name + N']'
from sys.foreign_keys fk
join sys.schemas s on ( s.schema_id = fk.schema_id )
join sys.objects o_p on ( o_p.object_id = fk.parent_object_id )
join sys.objects o_r on ( o_r.object_id = fk.referenced_object_id )
where
        ( o_p.schema_id = @s1 and o_r.schema_id = @s2 )
    or  ( o_p.schema_id = @s2 and o_r.schema_id = @s1 )

View more solutions