Solved

Drop and recreate constraints at later point

Posted on 2013-06-04
6
300 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
Comment Utility
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
Comment Utility
0
 
LVL 16

Author Comment

by:Easwaran Paramasivam
Comment Utility
@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
Comment Utility
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

772 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