Locating All Columns

Question

Locating All Columns

AndrewSQLDBA

SSC-Insane

Points: 21827
More actions
April 1, 2010 at 8:13 am

#146252

Hello Everyone
When one knows that a column in a table has FK Constraint, how can I easily determine all the tables that are involved?
What is the columns in the other tables are not named the same as what is in the parent table?
How can I determine all the table names, and all the column names in each table that are part of a Key Constraint.
Thanks in advance
Andrew SQLDBA

Viewing 4 posts - 1 through 3 (of 3 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

andrew sysforeignkeys can get you all the relationships, but it's all integers in there; you have to use some functions to get the data out where it's human-readable.

for example, if you have a specific column name in mind, you could use this to shed some light:

select

object_name(constid) as FKname,

object_name(fkeyid) as ChildTable,

col_name(fkeyid,fkey) as ChildColumn,

object_name(rkeyid) as ChildTable,

col_name(rkeyid,rkey) as ChildColumn

from sysforeignkeys

where col_name(fkeyid,fkey) = 'MyColumn'

or col_name(rkeyid,rkey) = 'MyColumn'

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 2

On similar lines of Lowell:

Try this:

select

o1.name as Referencing_Object_name

, c1.name as referencing_column_Name

, o2.name as Referenced_Object_name

, c2.name as Referenced_Column_Name

, s.name as Constraint_name

from sysforeignkeys fk

inner join sysobjects o1 on fk.fkeyid = o1.id

inner join sysobjects o2 on fk.rkeyid = o2.id

inner join syscolumns c1 on c1.id = o1.id and c1.colid = fk.fkey

inner join syscolumns c2 on c2.id = o2.id and c2.colid = fk.rkey

inner join sysobjects s on fk.constid = s.id

and o2.name='tablename' -- this predicate for a specific table

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

I have an excellent script for that. I also have a blog post explaining the workings of the script.

Check it out at:

http://jasonbrimhall.info/?p=263

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events