March 22, 2006 at 8:49 am
Hi all (SQL Server 2000).
I want to return a list of fields for a specified table and alongside each field I need to return if they are the primary key or foreign key.
I have got as far as return the list of fields and the indicator for PK (May not be right) but am stuck as to how to determin the FKs.
Code so far...
Select A.name,
C.name as datatype,
A.length,
A.prec,
A.scale,
Case A.colstat
When 9 then 1
Else 0
End as 'PK'
From syscolumns A Inner Join
sysobjects B on A.id = B.id Inner Join
systypes C on A.usertype = C.usertype
WHERE B.name = 'tblPerson'
Order by A.colOrder
Is the code to get the PK correct and can someone tell me how to get the FKs.
Thanks
CCB
March 22, 2006 at 9:46 am
This will get you foreign keys:
SELECT 'ForKey',
child_table child_table,
obj.name constraint_name,
child_column child_column,
child_pos pos,
parent_table parent_table,
parent_column parent_column
FROM (
SELECT tab1.name child_table,
col1.name child_column,
CASE col1.colid
WHEN ref.fkey1 THEN 1
WHEN ref.fkey2 THEN 2
WHEN ref.fkey3 THEN 3
WHEN ref.fkey4 THEN 4
WHEN ref.fkey5 THEN 5
WHEN ref.fkey6 THEN 6
WHEN ref.fkey7 THEN 7
WHEN ref.fkey8 THEN 8
WHEN ref.fkey9 THEN 9
WHEN ref.fkey10 THEN 10
WHEN ref.fkey11 THEN 11
WHEN ref.fkey12 THEN 12
WHEN ref.fkey13 THEN 13
WHEN ref.fkey14 THEN 14
WHEN ref.fkey15 THEN 15
WHEN ref.fkey16 THEN 16
END child_pos,
tab2.name parent_table,
col2.name parent_column,
ref.constid constraint_id,
CASE col2.colid
WHEN ref.rkey1 THEN 1
WHEN ref.rkey2 THEN 2
WHEN ref.rkey3 THEN 3
WHEN ref.rkey4 THEN 4
WHEN ref.rkey5 THEN 5
WHEN ref.rkey6 THEN 6
WHEN ref.rkey7 THEN 7
WHEN ref.rkey8 THEN 8
WHEN ref.rkey9 THEN 9
WHEN ref.rkey10 THEN 10
WHEN ref.rkey11 THEN 11
WHEN ref.rkey12 THEN 12
WHEN ref.rkey13 THEN 13
WHEN ref.rkey14 THEN 14
WHEN ref.rkey15 THEN 15
WHEN ref.rkey16 THEN 16
END parent_pos
FROM syscolumns col1,
sysobjects tab1,
syscolumns col2,
sysobjects tab2,
sysreferences ref
WHERE col1.id = ref.fkeyid
AND tab2.name In (Select 'Table' From DBADB.dbo.Object_Matrix Where OrderFulfillment = 'Y')
AND tab1.id = col1.id
AND col2.id = ref.rkeyid
AND tab2.id = col2.id
AND col1.colid IN (ref.fkey1, ref.fkey2,
ref.fkey3, ref.fkey4, ref.fkey5, ref.fkey6, ref.fkey7,
ref.fkey8, ref.fkey9, ref.fkey10, ref.fkey11,
ref.fkey12, ref.fkey13, ref.fkey14, ref.fkey15,
ref.fkey16)
AND col2.colid IN (ref.rkey1, ref.rkey2,
ref.rkey3, ref.rkey4, ref.rkey5, ref.rkey6, ref.rkey7,
ref.rkey8, ref.rkey9, ref.rkey10, ref.rkey11,
ref.rkey12, ref.rkey13, ref.rkey14, ref.rkey15,
ref.rkey16))
foreignkeycols,
sysobjects obj
WHERE child_pos = parent_pos
AND obj.id = constraint_id
ORDER BY 1, 2, 4
March 22, 2006 at 9:55 am
Thanks Scorpion.
I'll give it a go.
CCB
March 22, 2006 at 9:59 am
Ah, I just noticed....change the criteria for tablename to be a list of tablenames....either from sysobjects, or whereever......I had a tablelist of specific tables I referenced.....
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply