We help IT Professionals succeed at work.

Script Drop constraint statement for multiple environment

605 Views
Last Modified: 2010-03-19
I have some default contraints in a table(SQL SERVER 2005) which has auto generated names. I need to drop those constraints so that I can drop the columns. I need to create the scripts in my Dev environment and push it to the QA and Prod environment. Since the constraint name is auto generated so is there any way to script statement to drop those constraints from QA and Prod environment.
 
Comment
Watch Question

Top Expert 2007
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Jai STech Arch

Commented:
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...

Author

Commented:
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.
 
Top Expert 2007

Commented:
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 ...

Author

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

Author

Commented:
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'
Top Expert 2007

Commented:
... 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 ...

Author

Commented:
Thanks Yveau, I have posted my query..
Top Expert 2007

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

Author

Commented:
I would like to award Yveau 'B' grade. How do I do that?
Top Expert 2007

Commented:
Glad I could be of any help ...
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.