Dropping multiple foreign keys of unknown name

I have a DB schema generated by Hibernate. Hibernate generates FKs by assigning them a generated name like FK0123456789ABCDEF.

As part of the upgrade process of an application installed at a customer's site, I need to drop some FKs created by Hibernate long ago. I cannot be sure that their names remain the same from my local installation and the customer's site. Worse, their DBA won't allow me to trickle with their UAT and PROD Dbs with a Management Studio client: they want one script to run both on UAT and PROD without any error (violators will be rolled back).

I know how to indentify these FKs. FKs from tables labeled TA_xxx to tables labeled XTB_xx.

So I ran the following in local

SELECT
    name AS FkName,
    OBJECT_NAME(parent_object_id) AS ReferencingTable,
    OBJECT_NAME(referenced_object_id) AS ReferencedTable
FROM sys.foreign_keys
WHERE OBJECT_NAME(parent_object_id) LIKE 'TA_%'
AND OBJECT_NAME(referenced_object_id) LIKE 'XTB_%'

Result:

FK2A3B4D9E4EF09A0A  TA_BONDS    XTB_IMPORTS
FK2AC809114EF09A0A  TA_LOANS    XTB_IMPORTS
FKFCE5B2C695243BE8  TA_DEAL_SUBMISSIONS XTB_EXPORTS
FKFCE5B2C64757176B  TA_DEAL_SUBMISSIONS XTB_EXPORTS
FK2A52C11573F2E7FF  TA_DEALS    XTB_ORGANIZATIONS
FK2A52C11595243BE8  TA_DEALS    XTB_EXPORTS
FK2A52C1154757176B  TA_DEALS    XTB_EXPORTS

They are 7 both on my installation and on customer's site. I wanted to adapt a script like the following

DECLARE @fkConstraint SYSNAME
DECLARE @tbName SYSNAME

SELECT
    @fkConstraint= name,
    @tbName = OBJECT_NAME(parent_object_id)
FROM sys.foreign_keys
WHERE OBJECT_NAME(parent_object_id) LIKE 'TA_%' AND OBJECT_NAME(referenced_object_id) LIKE 'XTB_%'

DECLARE @DropStmt NVARCHAR(500)

SET @DropStmt = 'ALTER TABLE [dbo].'+@tbName+' DROP CONSTRAINT ' + @fkConstraint

EXEC (@DropStmt)
GO

I think I could repeat the above block 7 times to get rid of all FKs, but I'd ask if there is a more automated way, like "DROP CONSTRAINT ... WHERE IN (SELECT ...)"

Any ideas?

One way is dump the result of below query into a Temp Table and execute resulted rows one by one.

CREATE TABLE #foregnkeys
    (
      ROWID INT IDENTITY(1, 1),
      SQLStmt VARCHAR(3000)
    )

DECLARE @count INT= 0,
    @SQLStmt VARCHAR(3000)

INSERT  INTO #foregnkeys
        SELECT  'alter table [' + sc.name + '].['
                + OBJECT_NAME(fk.parent_object_id, DB_ID('DBNAME'))
                + '] drop constraint [' + fk.name + ']'
        FROM    DBNAME.sys.foreign_keys fk
                INNER JOIN DBNAME.sys.tables tbl ON tbl.[object_id] = fk.parent_object_id
                INNER JOIN DBNAME.sys.schemas sc ON sc.[schema_id] = tbl.[schema_id]
        WHERE   tbl.name LIKE 'TA_%'
                OR tbl.name LIKE 'XTB_%'    --- AND of your query is converted to OR

SELECT  @count = ( SELECT MAX(rowid) FROM #foregnkeys )

WHILE ( @count > 0 )  
    BEGIN

        SELECT  @SQLStmt = ( SELECT SQLStmt
                             FROM   #foregnkeys
                             WHERE  ROWID = @count
                           )

        PRINT ( Cast(@count as varchar)+'_ '+ @SQLStmt )
        EXEC ( @SQLStmt)

        SELECT  @count = @count - 1

    END      

DROP TABLE #foregnkeys

StackExchange Database Administrators Q#94953, answer score: 3