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