[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 524
  • Last Modified:

Script Drop constraint statement for multiple environment

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.
 
0
assyst
Asked:
assyst
  • 5
  • 5
1 Solution
 
YveauCommented:
Here's a script that will drop a column from a table and makes sure all constraints are dropped which are on that column first. Make it a procedure and run it once for every column you want to remove and you're done.


declare @Tab varchar(128)
declare @Col varchar(128)
declare @cons varchar(128)
declare @Cmd varchar(2000)


select  @Tab = 'Tab01'  -- Your tablename goes here
,       @Col = 'Col01' -- Your column name goes here

declare @ID int
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  name
        from    sysobjects obj
        inner   join sysconstraints con
                on obj.id = con.constid
        where   con.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

tested on SQL 2000 SP4 and SQL 2005 SP2. Both test ran against a table with one constraint on the column that was removed.

Hope this helps ...
0
 
Jai STech ArchCommented:
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...
0
 
assystAuthor 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.
 
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
YveauCommented:
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 ...
0
 
assystAuthor Commented:
Hi Yveau,  Your script deletes all the constraints from the table and does not depend on column name provided.
0
 
assystAuthor 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'
0
 
YveauCommented:
... 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:
http://www.experts-exchange.com/Community_Support/General/
read about your options here:
http://www.experts-exchange.com/help.jsp#hs5 or


Hope this helps ...
0
 
assystAuthor Commented:
Thanks Yveau, I have posted my query..
0
 
YveauCommented:
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

0
 
assystAuthor Commented:
I would like to award Yveau 'B' grade. How do I do that?
0
 
YveauCommented:
Glad I could be of any help ...
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now