Generate all database foreign keys

,

This script will create another script that has all foreign key DDL that exists in a given database.   You only have to run this, and then select the results of this script and paste it into another query window, or if for some reason you have lost your DDL this will enable you to retrieve it.

declare @User     varchar(128)
declare @TabName  varchar(128)
declare @RefName  varchar(128)
declare @FKName   varchar(128)
declare @STMT     varchar(5000)


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

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 

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


			PRINT @STMT

		Close ColCur
		Deallocate ColCur

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

close FKCur
deallocate FKCur

Rate

Share

Share

Rate