August 28, 2003 at 7:59 am
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
August 29, 2003 at 2:55 am
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.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply