Foreign Key DDL wizard

,

This script can be run when you want to do batch processing on a database, such as a dynamic truncate statement for all tables in your database.  This script will actually script all the Foreign Keys in your database, and then store them into a table called FOREIGN_KEY_TEMP.  It will then generate, and execute, all the ALTER TABLE statements needed to drop your foreign keys.  Then it will display the results, of the scripting done earlier to give you back your foreign keys.

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

Rate

Share

Share

Rate