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