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
