SQL remove autonamed constraints

Question

I have created a few constraints on my table with a script without specifying the constraint name. As a result, I ended up having constraints like FK__DOC_OBGS___kntr___54E63309 for example.

Is it possible to drop that constraints without specifying the exact constraint name ?

For example, something like this (which doesn't work)

ALTER TABLE DOC_OBGS_10 DROP CONSTRAINT LIKE 'FK__DOC_OBGS___kntr%'

The problem is that we have a lot of databases with this table and I need to remove all the constraints from the tables, but apparently the name is different for each table.

This is another good argument for why constraint should **always** be explicitly named according to a naming convention of your choice ... — marc_s, Jan 19 '13 at 11:04
Simple Solution => https://stackoverflow.com/a/49753774/185022 — AZ_, Apr 10 '18 at 12:33

score 9 · Accepted Answer · answered Jan 20 '13 at 09:35

DECLARE @sql1 NVARCHAR(MAX);
SELECT @sql1 = c
FROM
(
    SELECT 'ALTER TABLE DOC_INVS_1 DROP CONSTRAINT ' + CONSTRAINT_NAME + '; '
    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME = 'DOC_INVS_1'
    and CONSTRAINT_NAME LIKE 'FK__DOC_INVS___kntr%' 
) T(c);

EXEC(@sql1);

score 6 · Answer 2 · answered Jan 19 '13 at 10:48

6

DDL commands in SQL do not cooperate with like operator.

However, you may use information_schema views and easily build SQL for drop constraint commands, like this:

SELECT 'ALTER TABLE DOC_OBGS_10 DROP CONSTRAINT ' + CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS 
WHERE CONSTRAINT_NAME LIKE 'FK__DOC_OBGS___kntr%'

answered Jan 19 '13 at 10:48

pkmiec

2,594
18
16

Thanks. I used your idea and came with with this solution for my case: : SELECT 'ALTER TABLE DOC_INVS_1 DROP CONSTRAINT ' + CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME = 'DOC_INVS_1' and CONSTRAINT_NAME LIKE 'FK__DOC_INVS___kntr%' the problem is, how to exuce the query that is returned by the script? I tried using the Exec but I guess I have to include some brackets or something. Sorry, I'm newbie with SQL – Dimitar Tsonev Jan 19 '13 at 12:08
Which DBMS do you use? MS-SQL? – pkmiec Jan 19 '13 at 13:31
Just copy & paste the result rows, which together form your DDL script, into query window. Run script with F5 – pkmiec Jan 19 '13 at 13:56
I'm going to execute this script for all tables so I'm going to need something with the exec command – Dimitar Tsonev Jan 19 '13 at 13:59
Please look at my another answer http://stackoverflow.com/questions/14413573/generate-script-for-triggers-only-using-script-wizard/14413819#14413819. You can use idea from there to accumulate "drop constraint" scripts into single table. Later just select scripts from that table and use copy paste. – pkmiec Jan 19 '13 at 14:03
Thanks for the help. I figured it out how to execute the command – Dimitar Tsonev Jan 20 '13 at 09:36

score 0 · Answer 3 · answered Mar 26 '18 at 09:39

I faced a similar issue after using the Access to SQL Server upsizing tool. In my case I needed to loop all of the tables for matching constraints, which can be done like this:

DECLARE @sql1 NVARCHAR(MAX);

-- Concatenate all the drop statements in the inner loop into a single SQL string
SELECT @sql1 = STUFF((SELECT '; ' + a.t FROM 
(
    -- find all the constraints we're interested in and create a list of 
    -- SQL statements to drop them
    SELECT 'ALTER TABLE [' + p.name + '] DROP CONSTRAINT [' + o.name + ']' as t
    FROM sys.objects o
    left join sys.objects p on o.parent_object_id = p.object_id 
    WHERE o.name LIKE '%disallow_zero_length' 
) a
FOR XML PATH('')), 1, 2, '')

print @sql1

-- Execute the bit concatenated drop statement
EXEC(@sql1);

You can avoid the STUFF method in later versions of SQL Server, see this answer for more on that.

SQL remove autonamed constraints

3 Answers3

Linked