CODEWITHSUNDEEP
sqlsql-serverconstraints
Dimitar Tsonev
Dimitar Tsonev

Reputation: 3882

SQL remove autonamed constraints

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.

Upvotes: 13

Views: 6649

Answers (3)

tomRedox
tomRedox

Reputation: 30403

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.

Upvotes: 0

Dimitar Tsonev
Dimitar Tsonev

Reputation: 3882

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);

Upvotes: 10

pkmiec
pkmiec

Reputation: 2694

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%'

Upvotes: 6

Related Questions