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

Rate

4.67 (3)

Share

Share

Rate

4.67 (3)