Find if an unique index exists in MSSQL-CodePudding

I have a reason to drop a Unique constraint if it already exists, where I may be modifying a field that it references.

My basic attempt is:

IF EXISTS (SELECT OBJECTPROPERTY(OBJECT_ID(N'[dbo].[TableY].[UQ_X]'),'IsUniqueCnst'))
BEGIN
  ALTER TABLE [dbo].[TableY]
    DROP CONSTRAINT [UQ_X]
END

The OBJECT_ID call always returns null and the code proceeds to try to drop the constraint, whether [UQ_X] exists or not.

CodePudding user response：

Thanks to the clues from the comments, I have a working option.

The solution seems to be to look at the sys.key_constraints data

IF EXISTS( SELECT 1
            FROM sys.key_constraints k
            WHERE k.parent_object_id =OBJECT_ID(N'[dbo].[TableY]')
              AND k.[type] = 'UQ'
              AND k.[name] ='UQ_X')
BEGIN
 ALTER TABLE [dbo].[TableY]
    DROP CONSTRAINT [UQ_X]      
END