Solved

Drop and recreate constraints at later point

Posted on 2013-06-04
6
302 Views
Last Modified: 2013-07-07
I've script to drop all the constraints in my database. I would like to get TSQL scripts to recreate following constraints: PK, Default, Unique, NOT NULL, and CHECK constraints.

Please do assist.
0
Comment
Question by:Easwaran Paramasivam
  • 2
6 Comments
 
LVL 39

Assisted Solution

by:lcohan
lcohan earned 333 total points
ID: 39219286
For PK you can use a script like below to save them and add them back later:

SET NOCOUNT ON

-- save all PKeys definition and drop them for all tables.columns to be converted
declare @tabnam varchar(256),
      @colnam varchar(256),
      @chgto varchar(256),
      @Length varchar(256),
      @sqlstr varchar(2048)

declare @tab_list table (TableName varchar(256))
--, ColumnName varchar(256), ChangeTo varchar(256), Length varchar(256))

insert into @tab_list
--      select distinct tablename,ColumnName,ChangeTo,Length from UnicodeChangepointDataTypes
--            where comment <> 'Leave as is' and length > 0
--            order by tablename

select so1.name as tablename
      from sysobjects so, sysobjects so1
      where so.xtype='PK' and so1.id=so.parent_obj
      order by so1.name

create table _PKeys (
            table_qualifier varchar(256),
            table_owner varchar(256),
            table_name varchar(256),
            column_name varchar(256),
            key_seq varchar(256),
            pk_name varchar(256))

while (select count(*) from @tab_list) > 0
begin
      set @tabnam = (select top 1 TableName from @tab_list)
-- get all for @tabnam and @colnam
      declare @const_name varchar(256),
            @Sstr varchar (1024),
            @get_const varchar(1024)
      create table #pk_list (
            table_qualifier varchar(256),
            table_owner varchar(256),
            table_name varchar(256),
            column_name varchar(256),
            key_seq varchar(256),
            pk_name varchar(256))
      
      insert into #pk_list exec sp_pkeys @table_name = @tabnam
      insert into _PKeys select * from #pk_list
      set @const_name = (select pk_name from #pk_list where key_seq=1)
      set @sstr = 'ALTER TABLE ['+@tabnam+'] DROP CONSTRAINT '+(@const_name)
--      exec (@Sstr)
      print (@Sstr)
      print 'go'
      drop table #pk_list
      delete @tab_list where TableName = @tabnam
end

GO


--to add them back run:

SET NOCOUNT ON
-- add back to the database all saved PKeys
-- save _PKeys just in case
select distinct * into #PKeys from _PKeys

declare @tabname varchar(100),
      @keynam varchar(100),
      @Cstr varchar (100),
      @Clist varchar (1024),
      @key_seq tinyint,
      @PK_str varchar(2048)

set @clist = ''

while (select count(*) from #PKeys) > 0
begin

      set @tabname = (select top 1 table_name from #PKeys where key_seq=1)
      set @keynam = (select pk_name from #PKeys where table_name=@tabname and key_seq=1)
      select column_name,key_seq into #col_list from #PKeys where table_name=@tabname order by key_seq

      while (select count(*) from #col_list) > 0
      begin
            set @Cstr = (select top 1 column_name from #col_list)
            set @key_seq = (select top 1 key_seq from #col_list)

            if len(@Cstr)>0 and @key_seq = 1
                  set @Clist = @Cstr
            if len(@Cstr)>0 and @key_seq > 1
                  set @Clist = @Clist+' , '+@Cstr

            delete from #col_list where column_name=@Cstr
      end
      set @PK_str ='ALTER TABLE ['+@tabname+'] ADD CONSTRAINT ['+(@keynam)+'] PRIMARY KEY CLUSTERED ('+(@clist)+')'
      --exec (@PK_str)
      print (@pk_str)
      set @clist = ''
      drop table #col_list
      delete #PKeys where table_name = @tabname
end

drop table #PKeys

GO
0
 
LVL 39

Assisted Solution

by:lcohan
lcohan earned 333 total points
ID: 39219331
0
 
LVL 16

Author Comment

by:Easwaran Paramasivam
ID: 39219447
@lcohan: Thanks. Please do share scripts for rest of the constraints as well.
0
 
LVL 20

Accepted Solution

by:
Marten Rune earned 167 total points
ID: 39226843
If your PK is clustered, it makes no sense to drop it. Rather find a good fillfactor.
Why drop them, why not disable them and then enable them again. Less prone to errors in my book.
this looks right up your Alley: http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/fbf6b479-f588-454f-b5c0-e92bb04f1b66/
Please look att sommarskogs replies, he Always ahas a good Point.

Regards Marten
0

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

816 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now