April 22, 2012

sp_fkeys (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_fkeys(nvarchar @pktable_name
, nvarchar @pktable_owner
, nvarchar @pktable_qualifier
, nvarchar @fktable_name
, nvarchar @fktable_owner
, nvarchar @fktable_qualifier)

MetaData:

   
create procedure sys.sp_fkeys
(
@pktable_name sysname = null, -- Wildcard pattern matching IS NOT supported.
@pktable_owner sysname = null, -- Wildcard pattern matching IS NOT supported.
@pktable_qualifier sysname = null, -- Wildcard pattern matching IS NOT supported.
@fktable_name sysname = null, -- Wildcard pattern matching IS NOT supported.
@fktable_owner sysname = null, -- Wildcard pattern matching IS NOT supported.
@fktable_qualifier sysname = null -- Wildcard pattern matching IS NOT supported.
)
as
set nocount on

declare @pktable_id int
declare @fktable_id int

-- select 'XXX starting parameter analysis'
if (@pktable_name is null) and (@fktable_name is null)
begin -- If neither primary key nor foreign key table names given
raiserror (15252,-1,-1)
return
end

if @fktable_qualifier is not null
begin
if db_name() <> @fktable_qualifier
begin -- If qualifier doesn't match current database
raiserror (15250, -1,-1)
return
end
end

if @pktable_qualifier is not null
begin
if db_name() <> @pktable_qualifier
begin -- If qualifier doesn't match current database
raiserror (15250, -1,-1)
return
end
end

if @pktable_owner = ''
begin -- If empty owner name
select @pktable_id = object_id(quotename(@pktable_name))
end
else
begin
select @pktable_id = object_id(isnull(quotename(@pktable_owner), '') + '.' + quotename(@pktable_name))
end

if @fktable_owner = ''
begin -- If empty owner name
select @fktable_id = object_id(quotename(@fktable_name))
end
else
begin
select @fktable_id = object_id(isnull(quotename(@fktable_owner), '') + '.' + quotename(@fktable_name))
end

if @fktable_name is not null
begin
if @fktable_id is null
select @fktable_id = 0 -- fk table name is provided, but there is no such object
end

if @pktable_name is not null
begin
if @pktable_id is null
select @pktable_id = 0 -- pk table name is provided, but there is no such object

select
PKTABLE_QUALIFIER = convert(sysname,db_name()),
PKTABLE_OWNER = convert(sysname,schema_name(o1.schema_id)),
PKTABLE_NAME = convert(sysname,o1.name),
PKCOLUMN_NAME = convert(sysname,c1.name),
FKTABLE_QUALIFIER = convert(sysname,db_name()),
FKTABLE_OWNER = convert(sysname,schema_name(o2.schema_id)),
FKTABLE_NAME = convert(sysname,o2.name),
FKCOLUMN_NAME = convert(sysname,c2.name),
-- Force the column to be non-nullable (see SQL BU 325751)
KEY_SEQ = isnull(convert(smallint,k.constraint_column_id), sysconv(smallint,0)),
UPDATE_RULE = convert(smallint,
case f.update_referential_action
when 1 then 0
when 0 then 1
else f.update_referential_action
end),
DELETE_RULE = convert(smallint,
case f.delete_referential_action
when 1 then 0
when 0 then 1
else f.delete_referential_action
end),
FK_NAME = convert(sysname,object_name(f.object_id)),
PK_NAME = convert(sysname,i.name),
DEFERRABILITY = convert(smallint, 7) -- SQL_NOT_DEFERRABLE
from
sys.objects o1,
sys.objects o2,
sys.columns c1,
sys.columns c2,
sys.foreign_keys f inner join
sys.foreign_key_columns k on (k.constraint_object_id = f.object_id) inner join
sys.indexes i on (f.referenced_object_id = i.object_id and f.key_index_id = i.index_id)
where
o1.object_id = f.referenced_object_id and
(o1.object_id = @pktable_id) and
o2.object_id = f.parent_object_id and
(@fktable_id is null or o2.object_id = @fktable_id) and
c1.object_id = f.referenced_object_id and
c2.object_id = f.parent_object_id and
c1.column_id = k.referenced_column_id and
c2.column_id = k.parent_column_id
order by 5, 6, 7, 9, 8
end
else
begin
select
PKTABLE_QUALIFIER = convert(sysname,db_name()),
PKTABLE_OWNER = convert(sysname,schema_name(o1.schema_id)),
PKTABLE_NAME = convert(sysname,o1.name),
PKCOLUMN_NAME = convert(sysname,c1.name),
FKTABLE_QUALIFIER = convert(sysname,db_name()),
FKTABLE_OWNER = convert(sysname,schema_name(o2.schema_id)),
FKTABLE_NAME = convert(sysname,o2.name),
FKCOLUMN_NAME = convert(sysname,c2.name),
-- Force the column to be non-nullable (see SQL BU 325751)
KEY_SEQ = isnull(convert(smallint,k.constraint_column_id), sysconv(smallint,0)),
UPDATE_RULE = convert(smallint,
case ObjectProperty(f.object_id, 'CnstIsUpdateCascade')
when 1 then 0
else 1
end),
DELETE_RULE = convert(smallint,
case ObjectProperty(f.object_id, 'CnstIsDeleteCascade')
when 1 then 0
else 1
end),
FK_NAME = convert(sysname,object_name(f.object_id)),
PK_NAME = convert(sysname,i.name),
DEFERRABILITY = convert(smallint, 7) -- SQL_NOT_DEFERRABLE
from
sys.objects o1,
sys.objects o2,
sys.columns c1,
sys.columns c2,
sys.foreign_keys f inner join
sys.foreign_key_columns k on (k.constraint_object_id = f.object_id) inner join
sys.indexes i on (f.referenced_object_id = i.object_id and f.key_index_id = i.index_id)
where
o1.object_id = f.referenced_object_id and
(@pktable_id is null or o1.object_id = @pktable_id) and
o2.object_id = f.parent_object_id and
(o2.object_id = @fktable_id) and
c1.object_id = f.referenced_object_id and
c2.object_id = f.parent_object_id and
c1.column_id = k.referenced_column_id and
c2.column_id = k.parent_column_id
order by 1, 2, 3, 9, 4
end

No comments:

Post a Comment

Total Pageviews