Have Geek Brain

T-SQL Script to drop all relationships between a bunch of tables in a schema and another link in another schema?

I have a set of tables (like Account, Customer) in a schema (say dbo) and I have other tables (like Order, OrderItem) in a different schema (like inventory). There is a relationship between the order table and the Customer table. I want to delete all relationships between tables in the first schema (dbo) and tables in the second schema (inventory) without deleting the relationships between tables within the same schema.

Is it possible? Any help was appreciated.

+2


a source to share


4 answers


Use 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 into the template:



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

And execute with dynamic SQL.

+7


a source


you can get the available FKs existing in your db by below script and then remove them:

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


after that delete as shown below

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


a source


I won't swear that this will work on SQL 2005 as I don't have an instance to test, but if it does it will make it easier to copy / paste in SSMS. I'll leave it to repeat the results and execute if you need to.

Replace schema_1 and schema_2 with the names of the schemas with which you are trying to find a relationship between them.

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 )
+1


a source


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

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


a source






More articles:


All Articles