FKs

Does anyone have a script that will evaluate the foreign key relationships in a db and generate a script to drop and re-create them?you might be able to write the sql procedure by using the sysforeignkeys table|||What will this be used for ?|||declare @.cr nchar(2)
declare @.go nvarchar(8)

set @.cr = nchar(13)+nchar(10)
set @.go = @.cr + 'GO' + @.cr

declare @.tablename nvarchar(128)
declare @.column nvarchar(128)
declare @.schema nvarchar(128)
declare @.constraint nvarchar(128)
declare @.fktable nvarchar(128)
declare @.fkconstraint nvarchar(128)
declare @.onupdate varchar(9)
declare @.ondelete varchar(9)
declare @.comma char(1)

declare @.sql nvarchar(4000)

declare cstrts cursor local fast_forward read_only for
select
c.[TABLE_SCHEMA],
c.[TABLE_NAME],
u.CONSTRAINT_NAME
from [INFORMATION_SCHEMA].[COLUMNS] c
inner join [INFORMATION_SCHEMA].[KEY_COLUMN_USAGE] u
on c.[TABLE_NAME] = u.[TABLE_NAME]
and c.[COLUMN_NAME] = u.[COLUMN_NAME]
inner join [INFORMATION_SCHEMA].[table_constraints] t
on u.[CONSTRAINT_NAME] = t.[CONSTRAINT_NAME]
where t.[CONSTRAINT_TYPE] = 'FOREIGN KEY'

open cstrts

fetch next from cstrts
into @.schema, @.tablename, @.constraint

while @.@.fetch_status = 0
begin
select
@.fktable = u2.[TABLE_NAME],
@.fkconstraint = r.[UNIQUE_CONSTRAINT_NAME],
@.onupdate = r.[UPDATE_RULE],
@.ondelete = r.[DELETE_RULE],
@.column = u.[COLUMN_NAME]
from [INFORMATION_SCHEMA].[REFERENTIAL_CONSTRAINTS] r
inner join [INFORMATION_SCHEMA].[KEY_COLUMN_USAGE] u2
on r.[UNIQUE_CONSTRAINT_NAME] = u2.[CONSTRAINT_NAME]
inner join [INFORMATION_SCHEMA].[KEY_COLUMN_USAGE] u
on u.[CONSTRAINT_NAME] = r.[CONSTRAINT_NAME]
where r.[CONSTRAINT_NAME] = @.constraint

set @.sql =
'ALTER TABLE ['
+ @.schema
+ '].['
+ @.tablename
+ '] ADD CONSTRAINT ['
+ @.constraint
+ '] '
+ @.cr
+ 'FOREIGN KEY (['
+ @.column
+ ']) REFERENCES ['
+ @.fktable
+ '] ('

-- for each ordinal in the foreign key index...
declare idx cursor local fast_forward read_only for
select c.[COLUMN_NAME]
from [INFORMATION_SCHEMA].[COLUMNS] c
inner join [INFORMATION_SCHEMA].[KEY_COLUMN_USAGE] u
on c.[TABLE_NAME] = u.[TABLE_NAME]
and c.[COLUMN_NAME] = u.[COLUMN_NAME]
where u.[CONSTRAINT_NAME] = @.fkconstraint
order by u.[ORDINAL_POSITION]

open idx

set @.comma = ''

fetch next from idx
into @.column

while @.@.fetch_status = 0
begin
set @.sql = @.sql + @.comma + '[' + @.column + ']'
set @.comma = ','

fetch next from idx
into @.column

end

close idx
deallocate idx

set @.sql = @.sql + ') ON DELETE '
+ @.ondelete
+ ' ON UPDATE '
+ @.onupdate
+ @.go

print @.sql

fetch next from cstrts
into @.schema, @.tablename, @.constraint


end

close cstrts
deallocate cstrts
-------

See also my attachment to 'Restoring Databases from Win2000 to Win2003/Collation' which uses a drop and recreate to all forms of indexes to recollate a databse.

Hope This helps

HH|||Thanx, this is going to be used in conjunction with a proc that truncates all of the user tables in the db. I'm building a new system and need to frequently purge the data in the db to test data migration scripts and delete statements take too long to execute.