Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSrestoresavedforeignkeys

  No additional text.


Syntax
create procedure sys.sp_MSrestoresavedforeignkeys (
    @program_name   sysname = null
)
as
begin
    set nocount on
    declare @retcode                    int,
            @constraint_name            sysname,
            @parent_name                sysname,
            @parent_schema              sysname,
            @referenced_object_name     sysname,
            @referenced_object_schema   sysname,
            @is_disabled                bit,
            @is_not_for_replication     bit,
            @is_not_trusted             bit,
            @delete_referential_action  tinyint,
            @update_referential_action  tinyint,
            @transaction_opened         bit,
            @cursor_allocated           bit,
            @cursor_opened              bit,
            @command                    nvarchar(4000),
            @referencing_object_id      int,
            @referenced_object_id       int

    select @retcode = 0,
           @transaction_opened = 0,
           @cursor_allocated = 0,
           @cursor_opened = 0


    -- Security check
    exec @retcode = sys.sp_MSreplcheck_subscribe
    if @@error <> 0 or @retcode <> 0
        return 1

    -- There is nothing to do if any of the foreign key restoration
    -- system tables are missing
    if object_id('dbo.MSsavedforeignkeys', 'U') is null or
       object_id('dbo.MSsavedforeignkeycolumns', 'U') is null or
       object_id('dbo.MSsavedforeignkeyextendedproperties', 'U') is null
    begin
        return 0
    end

    if @program_name is null
    begin
        select @program_name = program_name
          from sys.sysprocesses
         where spid = @@spid
    end

    begin transaction
    save transaction sp_MSrestoresavedforeignkeys
    set @transaction_opened = 1

    declare hForeignKeys cursor local
        for
    select constraint_name,
           parent_schema,
           parent_name,
           referenced_object_schema,
           referenced_object_name,
           is_disabled,
           is_not_for_replication,
           is_not_trusted,
           delete_referential_action,
           update_referential_action
      from dbo.MSsavedforeignkeys
    if @@error <> 0 or @retcode <> 0 begin set @retcode = 1 goto Failure end
    set @cursor_allocated = 1

    open hForeignKeys
    if @@error <> 0 or @retcode <> 0 begin set @retcode = 1 goto Failure end
    set @cursor_allocated = 1

    fetch hForeignKeys into @constraint_name, @parent_schema, @parent_name,
        @referenced_object_schema, @referenced_object_name, @is_disabled,
        @is_not_for_replication, @is_not_trusted, @delete_referential_action,
        @update_referential_action

    while (@@fetch_status <> -1)
    begin

        -- Check if foreign key can be restored

        -- Is the foreign key already present?
        if object_id(quotename(@parent_schema) + N'.' + quotename(@constraint_name), 'F') is not null goto SkipForeignKey

        -- Is the referencing table present?
        select @referencing_object_id = object_id(quotename(@parent_schema) + N'.' + quotename(@parent_name), 'U')
        if @referencing_object_id is null goto SkipForeignKey

        -- Is the referenced table present?
        select @referenced_object_id =  object_id(quotename(@referenced_object_schema) + N'.' + quotename(@referenced_object_name), 'U')
        if @referenced_object_id is null goto SkipForeignKey

        -- Are all the referencing columns present?
        if exists (select *
                     from dbo.MSsavedforeignkeycolumns
                    where program_name = @program_name
                      and constraint_name = @constraint_name
                      and referencing_column_name not in
                            (select name
                               from sys.columns
                              where object_id = @referencing_object_id))
   goto SkipForeignKey

        -- Are all the referenced columns present?
        if exists (select *
                     from dbo.MSsavedforeignkeycolumns
                    where program_name = @program_name
                      and constraint_name = @constraint_name
                      and referenced_column_name not in
                            (select name
                               from sys.columns
                              where object_id = @referenced_object_id))
            goto SkipForeignKey

        -- Is there a candidate referenced index key present?
        if not exists (select key_constraints.name
                         from sys.index_columns index_columns
                   inner join sys.indexes indexes
                           on index_columns.index_id = indexes.index_id
                          and index_columns.object_id = indexes.object_id
                   inner join sys.key_constraints key_constraints
                           on indexes.name = key_constraints.name
                          and schema_name(key_constraints.schema_id) = @referenced_object_schema
                        where indexes.object_id = @referenced_object_id
                          and index_columns.column_id <> 0
                          and indexes.index_id not in
                                -- Indexes with columns that do not match
                                -- those saved in dbo.MSsavedforeignkeycolumns
                                (select index_columns.index_id
                                   from sys.index_columns index_columns
                             inner join sys.columns columns
                                     on index_columns.object_id = columns.object_id
                                    and index_columns.column_id = columns.column_id
                              left join
                                    (select referenced_column_name, constraint_column_id
                                       from dbo.MSsavedforeignkeycolumns
                                      where program_name = @program_name
                                        and constraint_name = @constraint_name
                                        and parent_schema = @parent_schema) foreignkeycolumns (referenced_column_name, constraint_column_id)
                                     on columns.name = foreignkeycolumns.referenced_column_name
                                    and index_columns.key_ordinal = foreignkeycolumns.constraint_column_id
                                  where index_columns.object_id = @referenced_object_id
                                    and index_columns.column_id <> 0
                                    and foreignkeycolumns.referenced_column_name is null)
                         -- The the number of columns in the index matches
                         -- that in the foreign key
                     group by key_constraints.name
                    having count(key_constraints.name) =
                        (select count(*)
                           from dbo.MSsavedforeignkeycolumns
                          where program_name = @program_name
                            and constraint_name = @constraint_name
                            and parent_schema = @parent_schema))
            goto SkipForeignKey

        select @command = N'execute sys.sp_MSscriptforeignkeyrestore @program_name = N''' + replace(@program_name, N'''', N'''''') +
            N''', @constraint_name = N''' + replace(@constraint_name, N'''', N'''''') +
            N''', @parent_schema = N''' + replace(@parent_schema, N'''', N'''''') +
            N''', @parent_name = N''' + replace(@parent_name, N'''', N'''''') +
            N''', @referenced_object_schema = N''' + replace(@referenced_object_schema, N'''', N'''''') +
            N''', @referenced_object_name = N''' + replace(@referenced_object_name, N'''', N'''''') +
     N''', @is_not_for_replication = ' + convert(nvarchar(2), @is_not_for_replication) +
            N', @is_not_trusted = ' + convert(nvarchar(2), @is_not_trusted) +
            N', @delete_referential_action = ' + convert(nvarchar(3), @delete_referential_action) +
            N', @update_referential_action = ' + convert(nvarchar(3), @update_referential_action)
        if @@error <> 0 or @retcode <> 0 begin set @retcode = 1 goto Failure end

        execute @retcode = sys.sp_execresultset @cmd = @command
        if @@error <> 0 or @retcode <> 0 begin set @retcode = 1 goto Failure end

        -- Disable foreign key if it was originally disabled
        if @is_disabled = 1
        begin
            set @command = N'alter table ' + quotename(@parent_schema) + N'.' + quotename(@parent_name) + ' nocheck constraint ' + quotename(@constraint_name)
            execute (@command)
            if @@error <> 0 or @retcode <> 0 begin set @retcode = 1 goto Failure end
        end

        -- Restore extended properties defined on foreign key
        execute @retcode = sys.sp_MSrestoresavedforeignkeyextendedproperties
            @program_name = @program_name,
            @constraint_name = @constraint_name,
            @parent_schema = @parent_schema,
            @parent_name = @parent_name
        if @@error <> 0 or @retcode <> 0 begin set @retcode = 1 goto Failure end

SkipForeignKey:

        fetch hForeignKeys into @constraint_name, @parent_schema, @parent_name,
            @referenced_object_schema, @referenced_object_name, @is_disabled,
            @is_not_for_replication, @is_not_trusted, @delete_referential_action,
            @update_referential_action
    end

    close hForeignKeys
    if @@error <> 0 or @retcode <> 0 begin set @retcode = 1 goto Failure end
    set @cursor_opened = 0

    deallocate hForeignKeys
    if @@error <> 0 or @retcode <> 0 begin set @retcode = 1 goto Failure end
    set @cursor_allocated = 0

    -- Cleanup foreign key tables now that everything is restored
    delete from dbo.MSsavedforeignkeys where program_name = @program_name
    if @@error <> 0 or @retcode <> 0 begin set @retcode = 1 goto Failure end

    delete from dbo.MSsavedforeignkeycolumns where program_name = @program_name
    if @@error <> 0 or @retcode <> 0 begin set @retcode = 1 goto Failure end

    delete from dbo.MSsavedforeignkeyextendedproperties where program_name = @program_name
    if @@error <> 0 or @retcode <> 0 begin set @retcode = 1 goto Failure end

    commit transaction
    set @transaction_opened = 0

Failure:
    if @cursor_opened = 1
    begin
        close hForeignKeys
    end

    if @cursor_allocated = 1
    begin
        deallocate hForeignKeys
    end

    if @transaction_opened = 1
    begin
        rollback transaction sp_MSrestoresavedforeignkeys
        commit transaction
    end
    return @retcode
end

 
Last revision 2008RTM
News

  Query a named instance
Sybase+ASE+silent+install
SQL 2008 R2
Deprecated procedures in SQL2008
Reporting Services item-level role definitions
Create all your missing indexes
Converting datetime field
Start MSSQL Server Profiler at time
Replicating MSSQL Server views
Exploring Microsoft Sharepoint
The OLE DB provider "SQLNCLI10" for linked server indicates.
Mobile solar charger
Oracle to SQL Server replication
Cannot insert the value NULL into column
Undocumented Microsoft SQL Server 2008
VMware
Zoekmachine optimalisatie
SQL Servers hidden objects
FckEditor
Flash in Ajax
De hype die AJAX heet
Barcode scannen
MySQL; Gratis, makkelijk en snel!
PHP
sIFR; de combinatie tussen HTML en Flash