SQL 2005

dastaub
dastaub used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Software Engineer
Top Expert 2011
Commented:
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'dbo.facility' AND COLUMN_NAME = 'Manager'
BEGIN
   ALTER TABLE dbo.Facility ADD Manager varbinary(50) NULL
END

Open in new window

Paul JacksonSoftware Engineer
Top Expert 2011
Commented:
IF NOT EXISTS (SELECT * FROM sys.objects WHERE name = 'DF_Facility_CliNo' AND type_desc = 'DEFAULT_CONSTRAINT')
BEGIN
   ALTER TABLE dbo.Facility ADD CONSTRAINT
      DF_Facility_CliNo DEFAULT 1 FOR CliNo
END

Open in new window

Author

Commented:
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

Author

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial