Drop column after checking regular constraints

-- use database
go
-- SQL 2005 and above
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
 /* exec sp_lib_drop_column 'constratintcheckhead','accountid','l' exec sp_lib_drop_column 'constratintcheckhead','accountid','d'
-- testing
create table constratintcheckhead ( accountid int not null primary key ,clientid int not null ) create table constraintcheckdetails ( accountid int not null ,notesid int not null primary key (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) with fillfactor = 90
create index IX_constratintcheckhead_clientid_accountid on constratintcheckhead(clientid,accountid) with fillfactor = 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
 */

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