April 22, 2012

sp_foreign_keys_rowset2 (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_foreign_keys_rowset2(nvarchar @foreignkey_tab_name
, nvarchar @foreignkey_tab_schema
, nvarchar @pk_table_name
, nvarchar @pk_table_schema
, nvarchar @pk_table_catalog)

MetaData:

   
create procedure sys.sp_foreign_keys_rowset2
(
@foreignkey_tab_name sysname,
@foreignkey_tab_schema sysname = null,
@pk_table_name sysname = null,
@pk_table_schema sysname = null,
@pk_table_catalog sysname = null
)
as
select
-- PK_TABLE_CATALOG = db_name(r.rkeydbid),
PK_TABLE_CATALOG = db_name(),
PK_TABLE_SCHEMA = schema_name(o1.schema_id),
PK_TABLE_NAME = o1.name,
PK_COLUMN_NAME = c1.name,
PK_COLUMN_GUID = convert(uniqueidentifier,null),
PK_COLUMN_PROPID = convert(int,null),
-- FK_TABLE_CATALOG = db_name(r.fkeydbid),
FK_TABLE_CATALOG = db_name(),
FK_TABLE_SCHEMA = schema_name(o2.schema_id),
FK_TABLE_NAME = o2.name,
FK_COLUMN_NAME = c2.name,
FK_COLUMN_GUID = convert(uniqueidentifier,null),
FK_COLUMN_PROPID = convert(int,null),
ORDINAL = convert(int,k.constraint_column_id),
-- UPDATE_RULE = CASE ObjectProperty(r.constid, 'CnstIsUpdateCascade')
UPDATE_RULE = CASE r.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,
-- DELETE_RULE = CASE ObjectProperty(r.constid, 'CnstIsDeleteCascade')
DELETE_RULE = CASE r.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,
PK_NAME = i.name,
-- FK_NAME = object_name(r.constid),
FK_NAME = object_name(r.object_id),
DEFERRABILITY = convert(smallint, 3) -- DBPROPVAL_DF_NOT_DEFERRABLE
from
sys.all_objects o1, -- ISSUE - PERF - do inner joins here instead of old join in where clause !!!
sys.all_objects o2, -- ISSUE - PERF - do inner joins here instead of old join in where clause !!!
sys.all_columns c1,
sys.all_columns c2,

-- sysreferences r,
sys.foreign_keys r inner join
sys.foreign_key_columns k on (k.constraint_object_id = r.object_id) inner join
sys.indexes i on (r.referenced_object_id = i.object_id and r.key_index_id = i.index_id)
where
(@pk_table_catalog is null or @pk_table_catalog = db_name())
and (@pk_table_name is null or @pk_table_name = o1.name) -- ISSUE - PERF - Do check against object_id, not against the names !!!
and (@pk_table_schema is null or @pk_table_schema = schema_name(o1.schema_id)) -- ISSUE - PERF - Do check against object_id, not against the names !!!
and ( @foreignkey_tab_name = o2.name) -- ISSUE - PERF - Do check against object_id, not against the names !!!
and (@foreignkey_tab_schema is null or @foreignkey_tab_schema = schema_name(o2.schema_id)) -- ISSUE - PERF - Do check against object_id, not against the names !!!

and
o1.object_id = r.referenced_object_id and
o1.object_id = c1.object_id and
c1.column_id = k.referenced_column_id and
o2.object_id = r.parent_object_id and
o2.object_id = c2.object_id and
c2.column_id = k.parent_column_id
order by 8,9,2,3,13

No comments:

Post a Comment

Total Pageviews