Need a Query for levels of tables

Question

Post reply

Need a Query for levels of tables

kishoremania

Ten Centuries

Points: 1059
More actions
August 3, 2011 at 10:19 am

#245575

Is there any query to get the levels of the tables in a database with RI.
for eg : TableA has a foreign key column of TableB, TableB has a foreign key column of TableC.
Table C is Level 0
Table B is Level 1
Table A is Level 2

Viewing 12 posts - 1 through 11 (of 11 total)

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

Yes there is. Check this one out.

http://jasonbrimhall.info/2011/07/11/foreign-key-hierarchy-update/

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

As the article explains, it covers both ancestry and hierarchy.

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

kishoremania Ten Centuries Points: 1059 More actions · Answer 3

Thanks jason for your instant reply.

Query executed successfully, but I am not getting any output. I gave one of my table in your script for "your Table".

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

You may want to test with other tables. If there are no FKs related to that table, you should get an empty result set.

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

kishoremania Ten Centuries Points: 1059 More actions · Answer 5

I tried for many tables, that too join tables which will definitely have foreign keys.

Sean Lange SSC Guru Points: 286590 More actions · Answer 6

Is it an actual foreign key as established on the table or is it only a logical foreign key?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

SQLRNNR SSC Guru Points: 281334 More actions · Answer 7

kishoremania (8/3/2011)
I tried for many tables, that too join tables which will definitely have foreign keys.

You might want to check in SSMS under the table properties as well as sys.foreign_keys

SELECT * FROM sys.foreign_keys

WHERE OBJECT_NAME(parent_object_id) = 'your table name'

OR OBJECT_NAME(referenced_object_id) = 'your table name'

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

Lowell SSC Guru Points: 323518 More actions · Answer 8

SQLRNNR (8/3/2011)
Yes there is. Check this one out.
http://jasonbrimhall.info/2011/07/11/foreign-key-hierarchy-update/

Nice script Jason, I had done something similar for Hierarchy, but mentally skipped over dependencies in the other direction.

i added that to my snippets, thanks!

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!

SQLRNNR SSC Guru Points: 281334 More actions · Answer 9

Lowell (8/3/2011)
SQLRNNR (8/3/2011)
Yes there is. Check this one out.
http://jasonbrimhall.info/2011/07/11/foreign-key-hierarchy-update/
Nice script Jason, I had done something similar for Hierarchy, but mentally skipped over dependencies in the other direction.
i added that to my snippets, thanks!

You are quite welcome. Remi (Ninja) sent me an update to support collations that are CS. I'll be posting the update soon.

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

kishoremania Ten Centuries Points: 1059 More actions · Answer 10

Thanks for your help. It really helped me. Finally I worked out and got a similar query.

Select

object_name(rkeyid) Parent_Table,

object_name(fkeyid) Child_Table,

object_name(constid) FKey_Name,

c1.name FKey_Col,

c2.name Ref_KeyCol

From

sys.sysforeignkeys s

Inner join sys.syscolumns c1

on ( s.fkeyid = c1.id And s.fkey = c1.colid )

Inner join syscolumns c2

on ( s.rkeyid = c2.id And s.rkey = c2.colid )

Order by Parent_Table,Child_Table

SQLRNNR SSC Guru Points: 281334 More actions · Answer 11

kishoremania (8/4/2011)
Thanks for your help. It really helped me. Finally I worked out and got a similar query.
Select
object_name(rkeyid) Parent_Table,
object_name(fkeyid) Child_Table,
object_name(constid) FKey_Name,
c1.name FKey_Col,
c2.name Ref_KeyCol
From
sys.sysforeignkeys s
Inner join sys.syscolumns c1
on ( s.fkeyid = c1.id And s.fkey = c1.colid )
Inner join syscolumns c2
on ( s.rkeyid = c2.id And s.rkey = c2.colid )
Order by Parent_Table,Child_Table

Since you are on SQL 2008, I'd recommend updating that query to use the new system objects in lieu of the soon to be deprecated objects.

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