Drop Column
-- use database
go
-- SQL 2005 and above
/*
exec sp_lib_drop_column 'constratintcheckhead','accountid','l'
exec sp_lib_drop_column 'constratintcheckhead','accountid','d'
-- testing
create table constratintcheckhead ( accountid int not nullprimary key ,clientid int not null )
create table constraintcheckdetails ( accountid intnot null ,notesid intnot null primarykey (accountid,notesid) )
alter table constratintcheckhead
add constraint DF_accountid default 0 for accountid
alter table constratintcheckhead
add constraint CK_accountid check (accountid >= 0 )
alter table constratintcheckhead
add constraint UQ_account Unique (accountid,clientid )
alter table constraintcheckdetails
add constraint FK_constraintcheckdetails_constratintcheckhead foreign key (accountid)
references constratintcheckhead (accountid)
create index IX_constratintcheckhead_clientid on constratintcheckhead(clientid) withfillfactor = 90
create index IX_constratintcheckhead_clientid_accountid on constratintcheckhead(clientid,accountid) withfillfactor = 90
drop table constratintcheckhead
drop table constraintcheckdetails
Description : Lists/Drops Constartints and indexes for a column and drops the column
Following constraints are checked only
Default,Check, Foreign Key, Primary Key,Unique Key
Indexes : Clustered or Non Clustered
This will not check column is replicated or part of full text etc
Only SQL 2005 and above
WARNING: Once constaints are dropped , column will be dropped
Assumes there are not more than 10 columns in an index
Usage : exec sp_lib_drop_column 'constratintcheckhead','accountid','l'
exec sp_lib_drop_column 'constratintcheckhead','accountid','d'
Input Parameters : @tablename , @columnname ,@flag char(1)
@flag -- 'l' -- list constraints only , 'd' -- drop column , 'b' -- list constraints and drop column
Output Parameters : None
Return Value : None
Record Set : None
Created Date : 10 Jan 2009
Created By : M A Srinivas
Created Version : 1.00
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_lib_drop_column]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_lib_drop_column]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure dbo.sp_lib_drop_column @tablename varchar(150), @columnname varchar(80) ,@flag char(1) = 'l'
as
set nocount on
if @flag not in ('l','d','b')
return
declare @slno int ,@maxslno int,@vchsql varchar(4000),@ci_name varchar(150),@ci_type char(1)
create table #temp_constraints
( tablename varchar(150),type_desc varchar(150),ci_name varchar(150),
columnname varchar(80),ci_type char(1),
parentablename varchar(150),parentcolumname varchar(80),slno int identity(1,1) primary key
)
declare @numbers table ( n int primary key )
insert into @numbers values (1)
insert into @numbers values (2)
insert into @numbers values (3)
insert into @numbers values (4)
insert into @numbers values (5)
insert into @numbers values (6)
insert into @numbers values (7)
insert into @numbers values (8)
insert into @numbers values (9)
insert into @numbers values (10)
--------------------------------------------------------------------------------------------------------------
insert into #temp_constraints (tablename,type_desc,ci_name,columnname,ci_type,parentablename,parentcolumname )
select object_name(parent_object_id) as tablename,
type_desc,
name as ci_name,
col_name(parent_object_id,parent_column_id) as columnname,
'N' as ci_type ,
'' as parentablename,
'' as parentcolumname
from sys.default_constraints
where object_name(parent_object_id) = @tablename
and col_name(parent_object_id,parent_column_id) = @columnname
union all
select object_name(parent_object_id) as tablename,
type_desc,
name as ci_name,
col_name(parent_object_id,parent_column_id) as columnname,
'N' as ci_type ,
'' as parentablename,
'' as parentcolumname
from sys.check_constraints
where object_name(parent_object_id) = @tablename
and col_name(parent_object_id,parent_column_id) = @columnname
union all
select object_name( parent_object_id) as tablename,
'FOREIGN KEY' as type_desc,
object_name(constraint_object_id) as ci_name,
col_name(parent_object_id,parent_column_id) as columnname,
'N' as ci_type,
object_name( referenced_object_id) as parentablename,
col_name(referenced_object_id,referenced_column_id) as parentcolumname
from sys.foreign_key_columns
where object_name( referenced_object_id) = @tablename
and col_name(referenced_object_id,referenced_column_id) = @columnname
union all
select object_name(i.object_id) as tablename,
case when o.type = 'PK' then 'PRIMARY KEY'
when o.type = 'UQ' then 'UNIQUE'
else 'INDEX ' end as type_desc,
i.name as ci_name,
t.index_column_name as columnname,
case when o.type in ('PK','UQ') then 'N' else 'I' end as ci_type,
'' as parentablename,
'' as parentcolumname
from sys.indexes i
left outer join
sys.objects o
on o.parent_object_id = i.object_id
and i.name = o.name
cross apply
(
select index_col(object_name(i.object_id),index_id,n) as index_column_name
from @numbers where index_col(object_name(i.object_id),index_id,n) = @columnname
) t
where object_name(i.object_id) = @tablename
--------------------------------------------------------------------------------------------------------------
if @flag in ('l','b')
select * from #temp_constraints order by slno
if @flag = 'l'
return
set @slno = 0
select @maxslno = max(slno) from #temp_constraints
while @slno < @maxslno
begin
set @slno = @slno + 1
select @tablename = tablename,
@ci_name = ci_name,
@ci_type = ci_type
from #temp_constraints
where slno = @slno
if @ci_type = 'N'
set @vchsql = 'alter table ' + @tablename + ' drop constraint ' + @ci_name
else if @ci_type = 'I'
set @vchsql = 'drop index ' + @tablename + '.' + @ci_name
-- print @vchsql
exec (@vchsql)
end
--------------------------------------------------------------------------------------------------------------
drop table #temp_constraints
return
--------------------------------------------------------------------------------------------------------------
grant exec on sp_lib_drop_column to public
go 
