May 29, 2012

sp_MSrestoresavedforeignkeys (Transact-SQL MetaData) Definition

Please note: that the following source code is provided and copyrighted by Microsoft and is for educational purpose only.
The meta data is from an SQL 2012 Server.

I have posted alot more, find the whole list here.

Goto Definition or MetaData

Definition:

sys.sp_MSrestoresavedforeignkeys(nvarchar @program_name)

MetaData:

 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

No comments:

Post a Comment

Total Pageviews