May 29, 2012

sp_MSscriptforeignkeyrestore (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_MSscriptforeignkeyrestore(nvarchar @program_name
, nvarchar @constraint_name
, nvarchar @parent_schema
, nvarchar @parent_name
, nvarchar @referenced_object_schema
, nvarchar @referenced_object_name
, bit @is_not_for_replication
, bit @is_not_trusted
, tinyint @delete_referential_action
, tinyint @update_referential_action)

MetaData:

 create procedure sys.sp_MSscriptforeignkeyrestore (  
@program_name sysname,
@constraint_name sysname,
@parent_schema sysname,
@parent_name sysname,
@referenced_object_schema sysname,
@referenced_object_name sysname,
@is_not_for_replication bit,
@is_not_trusted bit,
@delete_referential_action tinyint,
@update_referential_action tinyint
)
as
begin
set nocount on

declare @retcode int
declare @number_of_key_columns int

declare @script table (line_number int identity primary key, line nvarchar(4000))

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

select @number_of_key_columns = count(*)
from dbo.MSsavedforeignkeycolumns
where program_name = @program_name
and constraint_name = @constraint_name
and parent_schema = @parent_schema

insert into @script (line) values (N'alter table ' + quotename(@parent_schema) + N'.' + quotename(@parent_name))
if @is_not_trusted = 1
begin
insert into @script (line) values (N'with nocheck')
end
insert into @script (line) values (N'add constraint ' + quotename(@constraint_name) + N' foreign key (' )

insert into @script (line)
select quotename(referencing_column_name)
from dbo.MSsavedforeignkeycolumns
where program_name = @program_name
and constraint_name = @constraint_name
and parent_schema = @parent_schema
and constraint_column_id = 1

insert into @script (line)
select N', ' + quotename(referencing_column_name)
from dbo.MSsavedforeignkeycolumns
where program_name = @program_name
and constraint_name = @constraint_name
and parent_schema = @parent_schema
and constraint_column_id > 1
order by constraint_column_id asc

insert into @script (line) values (N') references ' + quotename(@referenced_object_schema) + N'.' + quotename(@referenced_object_name) + N' (')

insert into @script (line)
select quotename(referenced_column_name)
from dbo.MSsavedforeignkeycolumns
where program_name = @program_name
and constraint_name = @constraint_name
and parent_schema = @parent_schema
and constraint_column_id = 1

insert into @script (line)
select N', ' + quotename(referenced_column_name)
from dbo.MSsavedforeignkeycolumns
where program_name = @program_name
and constraint_name = @constraint_name
and parent_schema = @parent_schema
and constraint_column_id > 1
order by constraint_column_id asc

insert into @script (line)
select N') on delete ' +
case @delete_referential_action
when 1 then N'cascade '
when 2 then N'set null '
when 3 then N'set default '
else N'no action '
end +
'on update ' +
case @update_referential_action
when 1 then N'cascade '
when 2 then N'set null '
when 3 then N'set default '
else N'no action '
end +
case when @is_not_for_replication = 1 then N'not for replication'
else N''
end

select line from @script order by line_number asc

end

No comments:

Post a Comment

Total Pageviews