Results 1 to 5 of 5

Thread: FK's

  1. #1
    Join Date
    Sep 2003
    Posts
    364

    Unanswered: FK's

    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?

  2. #2
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    you might be able to write the sql procedure by using the sysforeignkeys table
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  3. #3
    Join Date
    Feb 2002
    Posts
    2,232
    What will this be used for ?

  4. #4
    Join Date
    Nov 2003
    Posts
    94
    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

  5. #5
    Join Date
    Sep 2003
    Posts
    364
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •