The postings on this site are my own and do not represent my Employer's positions, advice or strategies.

LifeAsBob - Blog



No Ads ever, except search!
Saturday, July 20, 2024 Login

SQL Server Drop Constraints unknown name 8/12/2021 6:01:02 AM
SQL Server will create constraints with a default name, that is not consistent across implementations if you do not specify a name.

Best practice would be to always specify a name.

But if you inherit a database across 1000's of locations and have to write update scripts, you may come across the need to drop a constraint for a column when you don't know the constraint name.

This script will find the constraint name based off the column and dynamically generate a drop statement for it.

SET @SelectStatement =  (SELECT 'ALTER TABLE [Machines] DROP CONSTRAINT ' + name

                                   FROM sysobjects               

                    WHERE parent_obj = object_id('TableName') AND xtype = 'D' AND name LIKE '%ColumnName%')

                           EXECUTE sp_executesql @SelectStatement

Blog Home