[MSSQL] Constraint 걸린 컬럼지우기

특정 테이블에 해당 컬럼에 대한 constraint가 존재하면 삭제후에 해당 컬럼을 삭제하도록 한다.

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='TB_GEOFENCE_SS' AND COLUMN_NAME='EMAIL_ALERT')
   BEGIN
             DECLARE @sysname sysname
             DECLARE @sql varchar(1000)
             SET @sysname = (select name from sysobjects where parent_obj in (select id from sysobjects where  name='tb_geofence_ss') and name like '%email%')
             set @sql = 'ALTER TABLE TB_GEOFENCE_SS DROP CONSTRAINT ' + @sysname
             exec (@sql)  

             set @sql = 'ALTER TABLE TB_GEOFENCE_SS DROP COLUMN EMAIL_ALERT'
             exec (@sql) 
   END
 GO