Solved

Drop and recreate constraints at later point

Posted on 2013-06-04
6
306 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 40

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 40

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to shrink a transaction log file down to a reasonable size.

713 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