Post reply

delete relation

  • January 1, 2011 at 5:12 am

    #243029

    a table in database can have a relation with itself.

    for example:

    i have a table with name: CrmPrpperties

    i have relation with name: fk_CrmProperties_CrmProperties

    i want to delete all of my relationsin in 600 tables or forignkeys that has a name like fk_

    _

    .

    can i do it?

    do you underestand me?

    i want to delete all of forin key in all of table that has relation with itself ?

  • January 2, 2011 at 10:07 pm

    #1268359

    You could do something like this:

    select 'ALTER TABLE [' + OBJECT_NAME(parent_object_id) + '] DROP CONSTRAINT [fk_'+ OBJECT_NAME(parent_object_id)+'_'+OBJECT_NAME(parent_object_id)+']'

    from sys.foreign_keys

    where name like 'fk_'+ OBJECT_NAME(parent_object_id)+'_'+OBJECT_NAME(parent_object_id)+'%'

    You should get a list of statements that give you alter table statements that you can then copy and paste to drop the constraints. You could do this in powershell as well, but I thought you may want it in TSQL.


    Ben Miller
    Microsoft Certified Master: SQL Server, SQL MVP
    @DBAduck - http://dbaduck.com

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply