CODEWITHSUNDEEP
sql-server
ProfK
ProfK

Reputation: 51063

Check for constraint on table

If I check for a column's existance as follows, before adding it, how would I do the equivalent for a UNIQUE constraint?

IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id=OBJECT_ID('[Table]') AND [Name]='Column')
    ALTER TABLE [Table] 
    ADD 
    [Column] varchar(500)

Upvotes: 0

Views: 204

Answers (2)

MikeW
MikeW

Reputation: 5922

Something like this for a constraint FK_myTable (SQL 2000):

if not exists ( select *
    from sysconstraints sc
    inner join sysobjects tbl on sc.id = tbl.id
    inner join sysobjects con on sc.constid = con.id
    where tbl.name = 'myTable' and con.name = 'FK_myTable' )

Upvotes: 1

jhale
jhale

Reputation: 1830

select * from sys.objects where type = 'uq' and parent_object_id = OBJECT_ID('[Table]')

Upvotes: 1

Related Questions