Script Drop constraint statement for multiple environment

hi
CREATE PROCEDURE AllcolumnConstrians
@tablename varchar(1000)
AS
SELECT 'DROP CONSTRAINT '+ [name] FROM sys.default_constraints
where objectid in (select objectid
from sys.tables where type_Desc = 'USER_TABLE'
and [name] = @tablename)

use it like
AllcolumnConstrians 'yourtablename'

this SP will give you the SQL statments that are need to drop the constriants...you cna then run them on your production / UAT DB...

I could get the Drop statement created by just getting into the SSMS > Table > Constraints and then selecting all the constraint for that table in 'Summary' window and generating the drop script. The real problem is whether there is any automated way of saying that drop the constraint for a particular column. If dropping the constraint can be done based on the column name then I just need to pass the column names which is same across multiple environment.
I am raising the points to max.
 

That is what my script does ! Dropping the columns and the according constraints, based on the name of the table and the column.
Just set the parameters @Tab and @Col, and the column will be dropped, no matter what constraint is on it ...
If you only want the constraints dropped, remove the last lines saying:

        select @Cmd = 'alter table '+@Tab+' drop column '+@Col
        select @Cmd
        exec (@Cmd)

Hope this helps ...

Hi Yveau,  Your script deletes all the constraints from the table and does not depend on column name provided.

I have got the script to do as intended although it's not clean
declare @Tab varchar(128)
declare @Col varchar(128)
declare @cons varchar(128)
declare @Cmd varchar(2000)
declare @str varchar(500)
declare @id int

select  @Tab = 'MemberMatch'  -- Your tablename goes here
select @Col = 'LearnerProgramStateID'

select  @ID = obj.id
from    sysobjects obj
inner   join syscolumns col
        on obj.id = col.id
where   col.name = @Col
and     obj.name = @Tab
and     obj.uid = 1

if (@ID is not NULL)
begin
        declare cons cursor
        for
        select  distinct s.name from sysobjects s
                        inner join sysconstraints c on
                              c.constID = s.ID
                        inner join syscolumns co on
                              co.ID = c.ID
                              where co.name in  ('LearnerProgramStateID', 'LearnerProgramStateTime')
                                    and  c.colID in (select colid from syscolumns where [name] in ('LearnerProgramStateID', 'LearnerProgramStateTime' )
                                  and ID = @ID )
            
            open    cons
        fetch   cons
        into    @Cons
select @@error, @@fetch_status

        while (@@error = 0) and (@@fetch_status = 0)
        begin
                select @Cmd = 'alter table '+@Tab+' drop constraint '+ @Cons
                select @Cmd
                exec (@cmd)
               
                fetch   cons
                into    @Cons
        end

        close cons
        deallocate cons

--        select @Cmd = 'alter table '+@Tab+' drop column '+@Col
--        select @Cmd
--        exec (@Cmd)
end
else
begin
        select  'Column not found.'
end


Moderator - Is there any way to share points with Yveau as the basic idea has been taken from him. Or is there any way it can be marked as 'Assisted Solution'

... you've got me there ... Should have done more extensive testing !
Thanks for the feedback !!!

If you want to draw the attention of the moderator, you might want to try here:
https://www.experts-exchange.com/Community_Support/General/
read about your options here:
https://www.experts-exchange.com/help.jsp#hs5 or


Hope this helps ...

Thanks Yveau, I have posted my query..

Well ... I guess I have to oppose to that ...
This is a typical "B" scenario in my humble opinion. I can see major parts of my script in the proposed solution ... I think I'm entitled to some points here. Assyst even pointed that out when saying: "...the basic idea has been taken from him."
... and I did certainly not abandon the question ...

Yveau

I would like to award Yveau 'B' grade. How do I do that?

Glad I could be of any help ...