Getting Metadata

• MegaDBA

  Hall of Fame

  Points: 3518

  More actions

  August 28, 2003 at 7:59 am

  #82835

  Hi everyone,

  I want to get the foreign key info in here. First, I'm getting PK info, I would like to do the same for FK's. Here's partial code below:

  AS SET NOCOUNT ON

  /* First select fetches table document elements and defines document format

  * through SQLXML explicit format column names. */

  SELECT distinct 2 as tag, NULL as parent,

  syso.name as [Table!2!Name],

  [Table!2!IsUpdateable] = CASE syso.xtype

  WHEN 'u' THEN 1

  WHEN 'v' THEN 0 END,

  NULL as [Column!3!Name],

  NULL as [Column!3!DataType],

  NULL as [Column!3!Length],

  NULL as [Column!3!IsNullable],

  NULL as [Column!3!ID],

  NULL as [Column!3!Description],

  NULL as [Column!3!IsIndexed],

  NULL as [Column!3!IsPrimaryKey],

  /* I would like to add a check for IsForeignKey key here

  * after the check for the IsPrimaryKey.

  */

  NULL as [Column!3!IsIdentity],

  NULL as [Procedure!4!Name],

  NULL as [Parameter!5!Name],

  NULL as [Parameter!5!DataType],

  NULL as [Parameter!5!Length],

  NULL as [Parameter!5!IsNullable],

  NULL as [Parameter!5!ID],

  NULL as [Parameter!5!IsOutParam],

  NULL as [Parameter!5!Description]

  FROM sysobjects syso

  WHERE syso.name != 'dtproperties'

  AND syso.name != 'meta'

  AND syso.name != 'sysconstraints'

  AND syso.name != 'syssegments'

  AND (syso.xtype = 'u' or syso.xtype = 'v')

  Can anyone help?

  
  

  Aurora

• brokenrulz

  SSCrazy

  Points: 2261

  More actions

  August 29, 2003 at 2:55 am

  #471938

  Information about foreign keys is stored in sysreferences table.

  The colums fkeyid and rkeyid are the objectid's of the referencing table and the referenced table respectively.

  Pay Respect to People on your way up. For you will meet the same People on your way down.

  
  He who knows others is learned but the wise one is one who knows himself.