Link to home
Start Free TrialLog in
Avatar of dastaub
dastaubFlag for United States of America

asked on

SQL 2005

How I can check if the field 'Manager' already exists before  I attempt to add the field?  

ALTER TABLE dbo.Facility ADD
      Manager varbinary(50) NULL
GO

How can I check if the constraint of a default value of 1 for the field CliNo already exists before I add the constraint.

ALTER TABLE dbo.Facility ADD CONSTRAINT
      DF_Facility_CliNo DEFAULT 1 FOR CliNo
GO
ASKER CERTIFIED SOLUTION
Avatar of Paul Jackson
Paul Jackson
Flag of United Kingdom of Great Britain and Northern Ireland image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Avatar of Paul Jackson
Paul Jackson
Flag of United Kingdom of Great Britain and Northern Ireland image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of dastaub
dastaub
Flag of United States of America image

ASKER

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'DF_Facility_CliNo' AND type_desc = 'DEFAULT_CONSTRAINT')
BEGIN
ALTER TABLE dbo.Facility DROP CONSTRAINT DF_Facility_CliNo
END
GO
ALTER TABLE dbo.Facility ADD CONSTRAINT DF_Facility_CliNo DEFAULT 1 FOR CliNo


IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'facility' AND COLUMN_NAME = 'Manager')
BEGIN
ALTER TABLE dbo.Facility DROP COLUMN Manager
END
GO
ALTER TABLE dbo.Facility ADD Manager varbinary(50) NULL
Avatar of dastaub
dastaub
Flag of United States of America image

ASKER

i added the drop to give the script the ability to edit the constraint or column rather than only add if not present.