FK - SQL help please

Question

FK - SQL help please

mw_sql_developer

SSCoach

Points: 19445
More actions
September 14, 2015 at 11:14 am

#311543

Select A.name, B.name
FROM
SYS.tables A
inner join
SYS.columns B on ( A.object_id = B.object_id )
ORDER BY 1 , 2
Can anyone help me so that i can add a 3 rd column to the SQL statement
where it shows if the column is a foriegn key and then where is it pointing to ?
Great and Thanks ahead

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Alan Burstein SSC Guru Points: 61141 More actions · Answer 1

What have you tried so far?

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Ed Wagner SSC Guru Points: 287026 More actions · Answer 2

Will this work for you?

SELECT so.name table_name, sc.name column_name, fk.name fk_name, so_fk.name references_table, sc_fk.name referenced_column

FROM sys.objects so

INNER JOIN sys.columns sc on sc.object_id = so.object_id

LEFT OUTER JOIN sys.foreign_key_columns fkc ON fkc.parent_object_id = sc.object_id

AND fkc.parent_column_id = sc.column_id

LEFT OUTER JOIN sys.foreign_keys fk ON fkc.constraint_object_id = fk.object_id

LEFT OUTER JOIN sys.objects so_fk ON fkc.referenced_object_id = so_fk.object_id

LEFT OUTER JOIN sys.columns sc_fk ON fkc.referenced_column_id = sc_fk.column_id

AND fkc.referenced_object_id = sc_fk.object_id

WHERE so.type = 'U'

ORDER BY so.name, sc.name;

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Ed Wagner (9/14/2015)
Will this work for you?
SELECT so.name table_name, sc.name column_name, fk.name fk_name, so_fk.name references_table, sc_fk.name referenced_column
FROM sys.objects so
INNER JOIN sys.columns sc on sc.object_id = so.object_id
LEFT OUTER JOIN sys.foreign_key_columns fkc ON fkc.parent_object_id = sc.object_id
AND fkc.parent_column_id = sc.column_id
LEFT OUTER JOIN sys.foreign_keys fk ON fkc.constraint_object_id = fk.object_id
LEFT OUTER JOIN sys.objects so_fk ON fkc.referenced_object_id = so_fk.object_id
LEFT OUTER JOIN sys.columns sc_fk ON fkc.referenced_column_id = sc_fk.column_id
AND fkc.referenced_object_id = sc_fk.object_id
WHERE so.type = 'U'
ORDER BY so.name, sc.name;

I'd use sys.tables instead of sys.objects as that restricts me to the user tables from the start.

Ed Wagner SSC Guru Points: 287026 More actions · Answer 4

Lynn Pettis (9/14/2015)
Ed Wagner (9/14/2015)
Will this work for you?
SELECT so.name table_name, sc.name column_name, fk.name fk_name, so_fk.name references_table, sc_fk.name referenced_column
FROM sys.objects so
INNER JOIN sys.columns sc on sc.object_id = so.object_id
LEFT OUTER JOIN sys.foreign_key_columns fkc ON fkc.parent_object_id = sc.object_id
AND fkc.parent_column_id = sc.column_id
LEFT OUTER JOIN sys.foreign_keys fk ON fkc.constraint_object_id = fk.object_id
LEFT OUTER JOIN sys.objects so_fk ON fkc.referenced_object_id = so_fk.object_id
LEFT OUTER JOIN sys.columns sc_fk ON fkc.referenced_column_id = sc_fk.column_id
AND fkc.referenced_object_id = sc_fk.object_id
WHERE so.type = 'U'
ORDER BY so.name, sc.name;
I'd use sys.tables instead of sys.objects as that restricts me to the user tables from the start.

Very true. I'm just so accustomed to using sys.objects for everything that I think I have a default set somewhere. 😛

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Ed Wagner (9/14/2015)
Lynn Pettis (9/14/2015)
Ed Wagner (9/14/2015)
Will this work for you?
SELECT so.name table_name, sc.name column_name, fk.name fk_name, so_fk.name references_table, sc_fk.name referenced_column
FROM sys.objects so
INNER JOIN sys.columns sc on sc.object_id = so.object_id
LEFT OUTER JOIN sys.foreign_key_columns fkc ON fkc.parent_object_id = sc.object_id
AND fkc.parent_column_id = sc.column_id
LEFT OUTER JOIN sys.foreign_keys fk ON fkc.constraint_object_id = fk.object_id
LEFT OUTER JOIN sys.objects so_fk ON fkc.referenced_object_id = so_fk.object_id
LEFT OUTER JOIN sys.columns sc_fk ON fkc.referenced_column_id = sc_fk.column_id
AND fkc.referenced_object_id = sc_fk.object_id
WHERE so.type = 'U'
ORDER BY so.name, sc.name;
I'd use sys.tables instead of sys.objects as that restricts me to the user tables from the start.
Very true. I'm just so accustomed to using sys.objects for everything that I think I have a default set somewhere. 😛

Also, I would use sys.foreign_key_columns in the query as well instead of linking back to sys.objects.

mw_sql_developer SSCoach Points: 19445 More actions · Answer 6

mw_sql_developer

SSCoach

Points: 19445

September 14, 2015 at 9:17 pm

#1826587

Cool! Thanks, worked well