Drop and recreate constraints at later point

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.
LVL 16
Easwaran ParamasivamAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Marten RuneConnect With a Mentor SQL Expert/Infrastructure ArchitectCommented:
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
 
lcohanConnect With a Mentor Database AnalystCommented:
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
 
lcohanConnect With a Mentor Database AnalystCommented:
0
 
Easwaran ParamasivamAuthor Commented:
@lcohan: Thanks. Please do share scripts for rest of the constraints as well.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.