drop one column
select replace(replace(replace(replace('
?default_commentalter table ?table_name drop constraint ?constraint_name
alter table ?table_name drop column ?column_name'
,'?default_comment', case when dc.object_id is null then '--' else '' end)
,'?constraint_name', isnull(dc.name, ''))
,'?table_name', object_name(c.object_id))
,'?column_name', c.name)
from sys.columns c
left join sys.default_constraints dc on dc.parent_object_id = c.object_id and dc.parent_column_id = c.column_id
where c.object_id = object_id('<table_name,,>')
and c.name in ('<column_name,,>')
drop multiple columns
with Lines as (select line = value from dbo.RegexSplit(
'up_Claim_Diagnosis e_code
Claim_Diagnosis e_code', '\r\n')
), Cols as (
select table_name = [0],
column_name = [1]
from Lines
cross apply dbo.RegexSplit(line, '\t')
pivot (max(value) for idx in ([0], [1])) p
)
select replace(replace(replace(replace('
?default_commentalter table ?table_name drop constraint ?constraint_name
alter table ?table_name drop column ?column_name'
,'?default_comment', case when dc.object_id is null then '--' else '' end)
,'?constraint_name', isnull(dc.name, ''))
,'?table_name', object_name(c.object_id))
,'?column_name', c.name)
from sys.columns c
left join sys.default_constraints dc on dc.parent_object_id = c.object_id and dc.parent_column_id = c.column_id
inner join Cols on object_id(Cols.table_name) = c.object_id and Cols.column_name = c.name
order by table_name, column_name