Solved

Drop and recreate constraints at later point

Posted on 2013-06-04
6
310 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sql server string_split 4 30
Sql server query 8 21
MS Access Duplicate Data Assistance 9 33
learning MS SSIS 13 26
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how the fundamental information of how to create a table.

710 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