June 4, 2012

sp_MStablerefs (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_MStablerefs(nvarchar @tablename
, nvarchar @type
, nvarchar @direction
, nvarchar @reftable
, int @flags)

MetaData:

   
create procedure sys.sp_MStablerefs
@tablename nvarchar(517),
@type nvarchar(20) = N'actualtables',
@direction nvarchar(20) = N'primary',
@reftable nvarchar(517) = null,
@flags int = 0
as
-- tablename: table whose references are being evaluated --
-- type : '[actual | all][tables | keys | keycols]'; all candidates, or only those actually referenced --
-- direction: look for references from @tablename to 'primary' table(s), or to @tablename from 'foreign' table(s) --
-- reftable : limit scope to this table, if non-null --
-- -- @flags added for DaVinci uses. If the bit isn't set, use 6.5 -- --
-- -- sp_MStablerefs '%s', null, 'both' -- --

create table #sprefs (
id int NOT NULL, -- id of reftable --
constid int NULL, -- id of key --
referenced bit NOT NULL -- well, is it? --
)

-- @flags is for daVinci --
if (@flags is null)
select @flags = 0

if (@tablename = N'?') begin
PRINT N''
PRINT N'sp_MStablerefs:'
PRINT N'@tablename nvarchar(257), -- table whose references are being evaluated -- '
PRINT N'@type nvarchar(20) = [actualtables], -- [[actual | all][tables | keys | keycols]]; all candidates, or only those actually referenced -- '
PRINT N'@direction nvarchar(20) = [primary], -- look for references from @tablename to [primary] or to @tablename from [foreign], or [both] -- '
PRINT N'@reftable nvarchar(257) = null -- limit scope to this table, if non-null -- '
return 0
end

if (lower(@direction) = N'both') begin
select
N'PK_Table' = PKT.name,
N'FK_Table' = FKT.name,
N'Constraint' = object_name(r.constid),
c.status,
cKeyCol1 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey1)),
cKeyCol2 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey2)),
cKeyCol3 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey3)),
cKeyCol4 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey4)),
cKeyCol5 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey5)),
cKeyCol6 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey6)),
cKeyCol7 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey7)),
cKeyCol8 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey8)),
cKeyCol9 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey9)),
cKeyCol10 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey10)),
cKeyCol11 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey11)),
cKeyCol12 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey12)),
cKeyCol13 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey13)),
cKeyCol14 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey14)),
cKeyCol15 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey15)),
cKeyCol16 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey16)),
cRefCol1 = convert(nvarchar(132), col_name(r.rkeyid, r.rkey1)),
cRefCol2 = convert(nvarchar(132), col_name(r.rkeyid, r.rkey2)),
cRefCol3 = convert(nvarchar(132), col_name(r.rkeyid, r.rkey3)),
cRefCol4 = convert(nvarchar(132), col_name(r.rkeyid, r.rkey4)),
cRefCol5 = convert(nvarchar(132), col_name(r.rkeyid, r.rkey5)),
cRefCol6 = convert(nvarchar(132), col_name(r.rkeyid, r.rkey6)),
cRefCol7 = convert(nvarchar(132), col_name(r.rkeyid, r.rkey7)),
cRefCol8 = convert(nvarchar(132), col_name(r.rkeyid, r.rkey8)),
cRefCol9 = convert(nvarchar(132), col_name(r.rkeyid, r.rkey9)),
cRefCol10 = convert(nvarchar(132), col_name(r.rkeyid, r.rkey10)),
cRefCol11 = convert(nvarchar(132), col_name(r.rkeyid, r.rkey11)),
cRefCol12 = convert(nvarchar(132), col_name(r.rkeyid, r.rkey12)),
cRefCol13 = convert(nvarchar(132), col_name(r.rkeyid, r.rkey13)),
cRefCol14 = convert(nvarchar(132), col_name(r.rkeyid, r.rkey14)),
cRefCol15 = convert(nvarchar(132), col_name(r.rkeyid, r.rkey15)),
cRefCol16 = convert(nvarchar(132), col_name(r.rkeyid, r.rkey16)),
N'PK_Table_Owner' = schema_name(sysoPKT.schema_id),
N'FK_Table_Owner' = schema_name(sysoFKT.schema_id),
N'DeleteCascade' = OBJECTPROPERTY( r.constid, N'CnstIsDeleteCascade'),
N'UpdateCascade' = OBJECTPROPERTY( r.constid, N'CnstIsUpdateCascade')
from dbo.sysreferences r, dbo.sysconstraints c, dbo.sysobjects PKT, sys.all_objects sysoPKT, dbo.sysobjects FKT, sys.all_objects sysoFKT
where r.constid = c.constid and (@tablename is null or
(r.rkeyid = object_id(@tablename) or r.fkeyid = object_id(@tablename))) and PKT.id = sysoPKT.object_id and FKT.id = sysoFKT.object_id
and PKT.id = r.rkeyid and FKT.id = r.fkeyid
return 0
end -- @direction = 'both' --

declare @id int, @refid int
select @id = object_id(@tablename), @refid = object_id(@reftable)
if (@tablename is not null and @id is null) begin
RAISERROR (15001, -1, -1, @tablename)
return 1
end
if (@reftable is not null and @refid is null) begin
RAISERROR (15001, -1, -1, @reftable)
return 1
end

declare @dotables bit, @doall bit, @doprimary bit, @docols bit
select @dotables = case when (@type like N'allt%' or @type like N'actualt%') then 1 else 0 end,
@doall = case when (@type like N'all%') then 1 else 0 end,
@doprimary = case when (@direction like N'p%') then 1 else 0 end,
@docols = case when (@type like N'%keycol%') then 1 else 0 end

-- If a specific @tablename specified, see if it has the kind of keys we want. --
-- If asking for references from @tablename to 'primary', we must have an FKEY; --
-- if asking for references to @tablename from 'foreign', we must have an active REFerence. --
if (@id is not null) begin
declare @wantkeytype varchar(32)
select @wantkeytype = case @doprimary when 1 then 'TableHasForeignKey' else 'TableHasForeignRef' end
if not exists (select * from dbo.sysobjects where id = @id and objectproperty(id, @wantkeytype) <> 0)
goto ReturnSet
end

if (@dotables = 1) begin
if (@doprimary = 1) begin
-- Get all candidate tables (those with Primary/Unique keys in sysconstraints). --
insert #sprefs
select distinct id, null, 0 from dbo.sysconstraints where status & 0x0f in (1, 2)

-- Update the referenced bit if this table references it. --
update #sprefs set referenced = 1
where id in (select rkeyid from dbo.sysreferences where fkeyid = @id)
end else begin
-- All user tables are foreign-key candidate tables. --
insert #sprefs
select distinct id, null, 0 from dbo.sysobjects where OBJECTPROPERTY(id, N'IsUserTable') = 1

-- Update the referenced bit if it references this table. --
update #sprefs set referenced = 1
where id in (select fkeyid from dbo.sysreferences where rkeyid = @id)
end -- direction --

end else begin -- keys --
if (@doprimary = 1) begin
-- Get all candidate tables (those with Primary/Unique keys in sysconstraints) and the keys. --
insert #sprefs
select distinct id, constid, 0 from dbo.sysconstraints where status & 0x0f in (1, 2)

-- Follow r.rkeyindid back to sysindexes to get the name and then 'rconstid' to see if this table references it. --
update #sprefs set referenced = 1 from #sprefs s, dbo.sysreferences r, dbo.sysindexes i
where r.fkeyid = @id
and i.id = r.rkeyid and i.indid = r.rkeyindid and i.status & 0x1800 <> 0
and s.constid = object_id(N'[' + REPLACE(i.name, N']', N']]') + N']')

end else begin
-- First add tables with FOREIGN keys defined. --
insert #sprefs
select distinct id, constid, 0 from dbo.sysconstraints where status & 0x0f in (3)

-- All user tables are foreign-key candidate tables, so add any tables we haven't yet, if @doall. --
-- (This would be used for 'push' key definition; defining FK's from the standpoint of the PK table). --
insert #sprefs
select distinct id, null, 0 from dbo.sysobjects where OBJECTPROPERTY(id, N'IsUserTable') = 1
and @doall = 1 and id not in (select id from #sprefs)

-- Update the referenced bit if it references this table. --
update #sprefs set referenced = 1
where constid in (select constid from dbo.sysreferences where rkeyid = @id)
end -- direction --
end -- tables or keys --

-- Exclude system and MS-internal objects, or tables/keys that aren't in the @reftable we want, if any specified. --
delete #sprefs where id in (select id from dbo.sysobjects where OBJECTPROPERTY(id, N'IsUserTable') <> 1 or category & 0x0002 <> 0)
or (@refid is not null and id != @refid)

-- Output --
ReturnSet:
if (@docols = 0) begin
if (@tablename is not null) begin
select candidate_table = N'[' + REPLACE(schema_name(syso.schema_id), N']', N']]') + N']' + N'.' + N'[' + REPLACE(object_name(o.id), N']', N']]') + N']',
candidate_key = case @dotables when 1 then N'N/A' else object_name(s.constid) end, s.referenced
from #sprefs s, dbo.sysobjects o, sys.all_objects syso where o.id = s.id and (@doall = 1 or s.referenced = 1) and o.id = syso.object_id
order by object_name(o.id), schema_name(syso.schema_id), object_name(s.constid)
end else begin
select candidate_table = N'[' + REPLACE(schema_name(syso.schema_id), N']', N']]') + N']' + N'.' + N'[' + REPLACE(object_name(o.id), N']', N']]') + N']',
candidate_key = case @dotables when 1 then N'N/A' else object_name(s.constid) end
from #sprefs s, dbo.sysobjects o, sys.all_objects syso where o.id = s.id and o.id = syso.object_id
order by object_name(o.id), schema_name(syso.schema_id), object_name(s.constid)
end
end else begin -- @docols = 1 --
-- This is currently just implemented for 'nonNULLtablename', 'actualkeycols', 'foreign'. --
select candidate_table = N'[' + REPLACE(schema_name(syso.schema_id), N']', N']]') + N']' + N'.' + N'[' + REPLACE(object_name(o.id), N']', N']]') + N']',
candidate_key = object_name(s.constid),
cKeyCol1 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey1)),
cKeyCol2 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey2)),
cKeyCol3 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey3)),
cKeyCol4 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey4)),
cKeyCol5 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey5)),
cKeyCol6 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey6)),
cKeyCol7 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey7)),
cKeyCol8 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey8)),
cKeyCol9 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey9)),
cKeyCol10 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey10)),
cKeyCol11 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey11)),
cKeyCol12 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey12)),
cKeyCol13 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey13)),
cKeyCol14 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey14)),
cKeyCol15 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey15)),
cKeyCol16 = convert(nvarchar(132), col_name(r.fkeyid, r.fkey16))
from #sprefs s, dbo.sysobjects o, dbo.sysreferences r, sys.all_objects syso
where o.id = s.id and r.constid = s.constid and s.referenced = 1 and o.id = syso.object_id
order by object_name(o.id), schema_name(syso.schema_id), object_name(s.constid)
end

No comments:

Post a Comment

Total Pageviews