edit: SQL2005: drop column and default

links

history

item nametags

==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
}}

some permissive license goes here
contact

tsunami