Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MStablerefs

  No additional text.


Syntax

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

 
Last revision 2008RTM
See also

  sp_MStablekeys (Procedure)
       



News

  Query a named instance
Sybase+ASE+silent+install
SQL 2008 R2
Deprecated procedures in SQL2008
Reporting Services item-level role definitions
Create all your missing indexes
Converting datetime field
Start MSSQL Server Profiler at time
Replicating MSSQL Server views
Exploring Microsoft Sharepoint
The OLE DB provider "SQLNCLI10" for linked server indicates.
Mobile solar charger
Oracle to SQL Server replication
Cannot insert the value NULL into column
Undocumented Microsoft SQL Server 2008
VMware
Zoekmachine optimalisatie
SQL Servers hidden objects
FckEditor
Flash in Ajax
De hype die AJAX heet
Barcode scannen
MySQL; Gratis, makkelijk en snel!
PHP
sIFR; de combinatie tussen HTML en Flash