0

Can I find all possible joins in one schema? For example up to level 2, 3 and 4.

I can do something like this:

*;with combination as (
 select distinct rn = DENSE_RANK() over (order by table_name), table_name from INFORMATION_SCHEMA.TABLES
)
select
 combination_no = ROW_NUMBER() Over (Order By NEWID()),
 c1.table_name, c2.table_name, c3.table_name, c4.TABLE_NAME
from combination c1, combination c2, combination c3, combination c4
where
 c1.rn < c2.rn and
 c2.rn < c3.rn and
 c3.rn < c4.rn
order by c1.rn, c2.rn, c3.rn, c4.rn

But, it's not what I meant. Can I generate these results only where there are connections?

enter image description here

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
Eazy1908
  • 13
  • 2
  • 1
    Does this answer your question? [How can I list all foreign keys referencing a given table in SQL Server?](https://stackoverflow.com/questions/483193/how-can-i-list-all-foreign-keys-referencing-a-given-table-in-sql-server) – allmhuran Jul 05 '20 at 09:55
  • 2
    @Easy1908 . . . There are basically an infinite number of *possible* joins -- after all, the conditions on the `join` can have basically any functions applied to them. Hence, your question is unclear. Also what you mean by "level" is quite unclear. – Gordon Linoff Jul 05 '20 at 11:43
  • Don't forget that a table can be related to itself, e.g. a parent/child relationship. How far down that rabbit hole do you want to keep joining? – HABO Jul 05 '20 at 12:13
  • The undocumented but super useful sp_msdependencies can be of assistance here. – allmhuran Jul 06 '20 at 03:07

2 Answers2

0

It sounds like you want to generate some output which shows the tables involved in foreign key relationships, and the columns included in those keys.

This information is stored in the sys.foreign_keys and sys.foreign_key_columns DMVs.

See This question

Also, don't write joins using where syntax. Use ANSI 92 syntax. Ie, instead of:

select * from T1, T2 where T1.col = T2.col

Use

select * from T1 join T2 on T1.col = T2.col
allmhuran
  • 4,154
  • 1
  • 8
  • 27
0

thanks for all responds. I did something like this:

 SELECT  obj.name AS FK_NAME,
    sch.name AS [schema_name],
    tab1.name AS tab,
    col1.name AS col,
    tab2.name AS [referenced_table],
    col2.name AS [referenced_column]
    into #temp
FROM sys.foreign_key_columns fkc
INNER JOIN sys.objects obj
    ON obj.object_id = fkc.constraint_object_id
INNER JOIN sys.tables tab1
    ON tab1.object_id = fkc.parent_object_id
INNER JOIN sys.schemas sch
    ON tab1.schema_id = sch.schema_id
INNER JOIN sys.columns col1
    ON col1.column_id = parent_column_id AND col1.object_id = tab1.object_id
INNER JOIN sys.tables tab2
    ON tab2.object_id = fkc.referenced_object_id
INNER JOIN sys.columns col2
    ON col2.column_id = referenced_column_id AND col2.object_id = tab2.object_id

and after that I can use:

select 'Select  top 10 * from '+ tab+ ' join ' + referenced_table +' on ' + col + ' = ' + referenced_table+'.' +referenced_column
    from #temp

Thats gave me all possible joins with other tables, and thats what I wanted. Now I'm still thinking, how to receive all possible joins when I want 2 and 3 joins in my query(without self join).

Eazy1908
  • 13
  • 2