Foreign Key DDL wizard

set NOCOUNT on
set CONCAT_NULL_YIELDS_NULL off

/******************************************************************************

This builds all existing constraint code and inserts it into a table 
(FOREIGN_KEY_TEMP) for storage

******************************************************************************/declare @User     varchar(128)
declare @TabName  varchar(128)
declare @RefName  varchar(128)
declare @FKName   varchar(128)
declare @STMT     varchar(5000)
declare @EmptyStr varchar(2)


PRINT '/* Generated FK script written by Jake Massey ' 
PRINT ' * Scripting took palce on ' + Cast(GetDate() as varchar(30))
PRINT ' */'

if exists 
(
  select * 
  from dbo.sysobjects so
  where so.id = OBJECT_ID(N'[dbo].[FOREIGN_KEY_TEMP]') 
      and OBJECTPROPERTY(so.id, N'IsUserTable') = 1
)
begin
TRUNCATE TABLE [dbo].[FOREIGN_KEY_TEMP]
end
else
begin
CREATE TABLE [dbo].[FOREIGN_KEY_TEMP] 
( CONSTRAINT_NAME varchar(128) NOT NULL , 
    FK_TEXT varchar(1000) NOT NULL )
end

declare FKCur cursor for
  select fk.name, t.name, u.name
  from sysobjects fk inner join sysobjects t
    on fk.parent_obj = t.id
    inner join sysusers u 
    on u.uid = t.uid
  where t.type = 'U'
  and fk.type = 'F'
  and t.name <> 'dtproperties'

open FKCur 

set @EmptyStr = ''

fetch next from FKCur into @FKName, @TabName, @User

while @@FETCH_STATUS = 0
  begin
select @STMT = 
'ALTER TABLE [' + @User + '].[' + @TabName + '] ADD 
CONSTRAINT [' + @FKName + '] FOREIGN KEY
 ( 
'

------------------------------------------------------
declare @Col1  varchar(128)
declare @Col2  varchar(128)
declare @Col3  varchar(128)
declare @Col4  varchar(128)
declare @Col5  varchar(128)
declare @Col6  varchar(128)
declare @Col7  varchar(128)
declare @Col8  varchar(128)
declare @Col9  varchar(128)
declare @Col10 varchar(128)
declare @Col11 varchar(128)
declare @Col12 varchar(128)
declare @Col13 varchar(128)
declare @Col14 varchar(128)
declare @Col15 varchar(128)
declare @Col16 varchar(128)

declare @Ref1  varchar(128)
declare @Ref2  varchar(128)
declare @Ref3  varchar(128)
declare @Ref4  varchar(128)
declare @Ref5  varchar(128)
declare @Ref6  varchar(128)
declare @Ref7  varchar(128)
declare @Ref8  varchar(128)
declare @Ref9  varchar(128)
declare @Ref10 varchar(128)
declare @Ref11 varchar(128)
declare @Ref12 varchar(128)
declare @Ref13 varchar(128)
declare @Ref14 varchar(128)
declare @Ref15 varchar(128)
declare @Ref16 varchar(128)

declare @RefTable varchar(128)
declare @RefOwner varchar(128)

declare @Update varchar(128)
declare @Delete varchar(128)


declare ColCur cursor for
  select col_name( object_id( sof.name ), sr.fkey1 ), 
     col_name( object_id( sof.name ), sr.fkey2 ),
     col_name( object_id( sof.name ), sr.fkey3 ),
     col_name( object_id( sof.name ), sr.fkey4 ),
     col_name( object_id( sof.name ), sr.fkey5 ),
     col_name( object_id( sof.name ), sr.fkey6 ),
     col_name( object_id( sof.name ), sr.fkey7 ),
     col_name( object_id( sof.name ), sr.fkey8 ),
     col_name( object_id( sof.name ), sr.fkey9 ),
     col_name( object_id( sof.name ), sr.fkey10 ),
     col_name( object_id( sof.name ), sr.fkey11 ),
     col_name( object_id( sof.name ), sr.fkey12 ),
     col_name( object_id( sof.name ), sr.fkey13 ),
     col_name( object_id( sof.name ), sr.fkey14 ),
     col_name( object_id( sof.name ), sr.fkey15 ),
     col_name( object_id( sof.name ), sr.fkey16 ),
 ----------------------------------------------
     col_name( object_id( sor.name ), sr.rkey1 ), 
     col_name( object_id( sor.name ), sr.rkey2 ),
     col_name( object_id( sor.name ), sr.rkey3 ),
     col_name( object_id( sor.name ), sr.rkey4 ),
     col_name( object_id( sor.name ), sr.rkey5 ),
     col_name( object_id( sor.name ), sr.rkey6 ),
     col_name( object_id( sor.name ), sr.rkey7 ),
     col_name( object_id( sor.name ), sr.rkey8 ),
     col_name( object_id( sor.name ), sr.rkey9 ),
     col_name( object_id( sor.name ), sr.rkey10 ),
     col_name( object_id( sor.name ), sr.rkey11 ),
     col_name( object_id( sor.name ), sr.rkey12 ),
     col_name( object_id( sor.name ), sr.rkey13 ),
     col_name( object_id( sor.name ), sr.rkey14 ),
     col_name( object_id( sor.name ), sr.rkey15 ),
     col_name( object_id( sor.name ), sr.rkey16 ),
 sor.name, su.name,
 CASE WHEN (objectproperty(constid, 'CnstIsUpdateCascade') = 1)
   THEN 'ON UPDATE CASCADE' ELSE ' ' END,
 CASE WHEN (objectproperty(constid, 'CnstIsDeleteCascade') = 1)
   THEN 'ON DELETE CASCADE' ELSE ' ' END
  from sysreferences sr inner join sysobjects sof on 
sof.id = sr.fkeyid
  inner join sysobjects sor on 
sor.id = sr.rkeyid
  inner join sysusers su on sor.uid = su.uid
  where sr.constid = object_id(@FKName)

open ColCur
  
        fetch next from ColCur into @Col1 , @Col2 , @Col3 , @Col4 , @Col5 , @Col6 , @Col7 ,
@Col8 , @Col9 , @Col10, @Col11, @Col12, @Col13, @Col14,
@Col15, @Col16,
@Ref1 , @Ref2 , @Ref3 , @Ref4 , @Ref5 , @Ref6 , @Ref7 ,
@Ref8 , @Ref9 , @Ref10, @Ref11, @Ref12, @Ref13, @Ref14,
@Ref15, @Ref16,
@RefTable, @RefOwner,
@Update, @Delete
if @Col1 = NULL 
set @Col1 = @EmptyStr
if @Col2 = NULL
set @Col2 = @EmptyStr
if @Col3 = NULL
set @Col3 = @EmptyStr
if @Col4 = NULL
set @Col4 = @EmptyStr
if @Col5 = NULL
set @Col5 = @EmptyStr
if @Col6 = NULL
set @Col6 = @EmptyStr
if @Col7 = NULL
set @Col7 = @EmptyStr
if @Col8 = NULL
set @Col8 = @EmptyStr
if @Col9 = NULL
set @Col9 = @EmptyStr
if @Col10 = NULL
set @Col10 = @EmptyStr
if @Col11 = NULL
set @Col11 = @EmptyStr
if @Col12 = NULL
set @Col12 = @EmptyStr
if @Col13 = NULL
set @Col13 = @EmptyStr
if @Col14 = NULL
set @Col14 = @EmptyStr
if @Col15 = NULL
set @Col15 = @EmptyStr
if @Col16 = NULL
set @Col16 = @EmptyStr
------------------------------
if @Ref1 = NULL 
set @Ref1 = @EmptyStr
if @Ref2 = NULL
set @Ref2 = @EmptyStr
if @Ref3 = NULL
set @Ref3 = @EmptyStr
if @Ref4 = NULL
set @Ref4 = @EmptyStr
if @Ref5 = NULL
set @Ref5 = @EmptyStr
if @Ref6 = NULL
set @Ref6 = @EmptyStr
if @Ref7 = NULL
set @Ref7 = @EmptyStr
if @Ref8 = NULL
set @Ref8 = @EmptyStr
if @Ref9 = NULL
set @Ref9 = @EmptyStr
if @Ref10 = NULL
set @Ref10 = @EmptyStr
if @Ref11 = NULL
set @Ref11 = @EmptyStr
if @Ref12 = NULL
set @Ref12 = @EmptyStr
if @Ref13 = NULL
set @Ref13 = @EmptyStr
if @Ref14 = NULL
set @Ref14 = @EmptyStr
if @Ref15 = NULL
set @Ref15 = @EmptyStr
if @Ref16 = NULL
set @Ref16 = @EmptyStr
------------------------------
if @Col1 <> @EmptyStr 
  begin
  set @STMT = @STMT + '  [' + @Col1 + ']' + Char(13)
 end
if @Col2 <> @EmptyStr
 begin
  set @STMT = @STMT + ', [' + @Col2 + ']' + Char(13)
 end
if @Col3 <> @EmptyStr
 begin
  set @STMT = @STMT + ', [' + @Col3 + ']' + Char(13)
 end
if @Col4 <> @EmptyStr
 begin
  set @STMT = @STMT + ', [' + @Col4 + ']' + Char(13)
 end
if @Col5 <> @EmptyStr
 begin
  set @STMT = @STMT + ', [' + @Col5 + ']' + Char(13)
 end
if @Col6 <> @EmptyStr
 begin
  set @STMT = @STMT + ', [' + @Col6 + ']' + Char(13)
 end
if @Col7 <> @EmptyStr
 begin
  set @STMT = @STMT + ', [' + @Col7 + ']' + Char(13)
 end
if @Col8 <> @EmptyStr
 begin
  set @STMT = @STMT + ', [' + @Col8 + ']' + Char(13)
 end
if @Col9 <> @EmptyStr
 begin
  set @STMT = @STMT + ', [' + @Col9 + ']' + Char(13)
 end
if @Col10 <> @EmptyStr 
 begin
  set @STMT = @STMT + ', [' + @Col10 + ']' + Char(13)
 end
if @Col11 <> @EmptyStr
 begin
  set @STMT = @STMT + ', [' + @Col11 + ']' + Char(13)
 end
if @Col12 <> @EmptyStr
 begin
  set @STMT = @STMT + ', [' + @Col12 + ']' + Char(13)
 end
if @Col13 <> @EmptyStr
 begin
  set @STMT = @STMT + ', [' + @Col13 + ']' + Char(13)
 end
if @Col14 <> @EmptyStr
 begin
  set @STMT = @STMT + ', [' + @Col14 + ']' + Char(13)
 end
if @Col15 <> @EmptyStr
 begin
  set @STMT = @STMT + ', [' + @Col15 + ']' + Char(13)
 end
if @Col16 <> @EmptyStr
 begin
  set @STMT = @STMT + ', [' + @Col16 + ']' + Char(13)
 end
---------------------------------------------------------------
set @STMT = @STMT + 
' )REFERENCES [' + @RefOwner + '].[' + @RefTable + '] ( 
'
---------------------------------------------------------------
if @Ref1 <> @EmptyStr 
  begin
  set @STMT = @STMT + '  [' + @Ref1 + ']' + Char(13)
 end
if @Ref2 <> @EmptyStr
 begin
  set @STMT = @STMT + ', [' + @Ref2 + ']' + Char(13)
 end
if @Ref3 <> @EmptyStr
 begin
  set @STMT = @STMT + ', [' + @Ref3 + ']' + Char(13)
 end
if @Ref4 <> @EmptyStr
 begin
  set @STMT = @STMT + ', [' + @Ref4 + ']' + Char(13)
 end
if @Ref5 <> @EmptyStr
 begin
  set @STMT = @STMT + ', [' + @Ref5 + ']' + Char(13)
 end
if @Ref6 <> @EmptyStr
 begin
  set @STMT = @STMT + ', [' + @Ref6 + ']' + Char(13)
 end
if @Ref7 <> @EmptyStr
 begin
  set @STMT = @STMT + ', [' + @Ref7 + ']' + Char(13)
 end
if @Ref8 <> @EmptyStr
 begin
  set @STMT = @STMT + ', [' + @Ref8 + ']' + Char(13)
 end
if @Ref9 <> @EmptyStr
 begin
  set @STMT = @STMT + ', [' + @Ref9 + ']' + Char(13)
 end
if @Ref10 <> @EmptyStr
 begin
  set @STMT = @STMT + ', [' + @Ref10 + ']' + Char(13)
 end
if @Ref11 <> @EmptyStr
 begin
  set @STMT = @STMT + ', [' + @Ref11 + ']' + Char(13)
 end
if @Ref12 <> @EmptyStr
 begin
  set @STMT = @STMT + ', [' + @Ref12 + ']' + Char(13)
 end
if @Ref13 <> @EmptyStr
 begin
  set @STMT = @STMT + ', [' + @Ref13 + ']' + Char(13)
 end
if @Ref14 <> @EmptyStr
 begin
  set @STMT = @STMT + ', [' + @Ref14 + ']' + Char(13)
 end
if @Ref15 <> @EmptyStr
 begin
  set @STMT = @STMT + ', [' + @Ref15 + ']' + Char(13)
 end
if @Ref16 <> @EmptyStr
 begin
  set @STMT = @STMT + ', [' + @Ref16 + ']' + Char(13)
 end
------------------------------------------------------------------
set @STMT = @STMT + ') ' + @Update + ' ' + @Delete 

INSERT INTO FOREIGN_KEY_TEMP values ( @FKName, @STMT )

Close ColCur
Deallocate ColCur

   fetch next from FKCur into @FKName, @TabName, @User
  end 

close FKCur
deallocate FKCur


/******************************************************************************

This will actually drop your constraints......

******************************************************************************/
declare @DeleteSTMT varchar(1000)


PRINT '/* Generated FK DROP script written by Jake Massey' 
PRINT ' * Scripting took palce on ' + Cast(GetDate() as varchar(30))
PRINT ' */'

declare FKDropCur cursor for 
select su.name, so.name, fk.name
from sysobjects so inner join sysobjects fk on fk.parent_obj = so.id
inner join sysusers su on su.uid = so.uid
where so.type = 'U'
    and so.name <> 'dtproperties'
    and fk.type = 'F'

open FKDropCur

fetch next from FKDropCur into @User, @TabName, @FKName
while @@FETCH_STATUS = 0 
begin
set @DeleteSTMT =  'ALTER TABLE [' + @User + '].[' + @TabName + 
      '] DROP CONSTRAINT ' + @FKName  
exec( @DeleteSTMT )
PRINT 'ALTERED TABLE [' + @User + '].[' + @TabName + '] DROPPED CONSTRAINT [' 
   + @FKName + ']'

fetch next from FKDropCur into @User, @TabName, @FKName
end

close FKDropCur
deallocate FKDropCur


select FK_TEXT from FOREIGN_KEY_TEMP