sql alter

--   DROP CONSTRAINT --
IF EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(N'[dbo].[FK_EmailServicesSendActivityLUT_EmailServicesContactLUT]')
 AND parent_object_id = OBJECT_ID(N'[dbo].[EmailServicesCampaignContact]'))
   BEGIN
    ALTER TABLE [dbo].[EmailServicesCampaignContact]
     DROP CONSTRAINT [FK_EmailServicesSendActivityLUT_EmailServicesContactLUT]
   END



--   DROP COLUMN --
IF EXISTS (
  SELECT *
  FROM   sys.columns   WHERE  object_id = OBJECT_ID(N'EmailServicesContactLUT') AND name = 'EmailServicesContactLUTId'
)
Begin
ALTER TABLE dbo.EmailServicesCampaignContact DROP COLUMN EmailServicesContactLUTId
End


--   DROP TABLE --
IF (EXISTS (SELECT *
                 FROM INFORMATION_SCHEMA.TABLES
                 WHERE TABLE_SCHEMA = 'dbo'
                 AND  TABLE_NAME = 'EmailServicesContactLUT'))
Begin
 DROP TABLE EmailServicesContactLUT
End



--  Add column
IF NOT EXISTS (
  SELECT *
  FROM   sys.columns   WHERE  object_id = OBJECT_ID(N'EmailServicesCampaignContact') AND name = 'ConstituentLUTId'
)
Begin
ALTER TABLE EmailServicesCampaignContact  Add  ConstituentLUTId int NOT NULL
End


--  ADD  CONSTRAINT
IF EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(N'[dbo].[FK_EmailServicesCampaignContact_ConstituentLUT]')
 AND parent_object_id = OBJECT_ID(N'[dbo].[EmailServicesCampaignContact]'))
   BEGIN
ALTER TABLE [dbo].[EmailServicesCampaignContact]  WITH CHECK ADD  CONSTRAINT [FK_EmailServicesCampaignContact_ConstituentLUT]
FOREIGN KEY([ConstituentLUTId])
REFERENCES [dbo].[ConstituentLUT] ([ConstituentLUTId])
ALTER TABLE [dbo].[EmailServicesCampaignContact] CHECK CONSTRAINT [FK_EmailServicesCampaignContact_ConstituentLUT]
End



-- DROP COLUMN
IF EXISTS (
  SELECT *
  FROM   sys.columns   WHERE  object_id = OBJECT_ID(N'EmailServicesCampaignContact') AND name = 'EmailAddress'
)
Begin
ALTER TABLE dbo.EmailServicesCampaignContact DROP COLUMN EmailAddress
End



-- DROP COLUMN
IF EXISTS (
  SELECT *
  FROM   sys.columns   WHERE  object_id = OBJECT_ID(N'EmailServicesCampaignContact') AND name = 'ConstituentLUT'
)
Begin
ALTER TABLE dbo.EmailServicesCampaignContact DROP COLUMN ConstituentLUT
End