I am using SQL server 2008. I need to find if default value constraint does not exist then create it. Here is what I have tried.
x
IF (NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS WHERE CONSTRAINT_NAME ='MyConstraint'))
BEGIN
ALTER TABLE [XX] ADD CONSTRAINT [MyConstraint] DEFAULT ((-1)) FOR [XXXX]
END
GO
Advertisement
Answer
if not exists (
select *
from sys.all_columns c
join sys.tables t on t.object_id = c.object_id
join sys.schemas s on s.schema_id = t.schema_id
join sys.default_constraints d on c.default_object_id = d.object_id
where t.name = 'table'
and c.name = 'column'
and s.name = 'schema')
.