How can one find out if two tables can be joinned dynamically

Hi All,

Given a table, is there a way to programmatically find out what other tables and views it can be joinned with? Essentially, given two tables, two views, or a table and a view, I want to be able to find out if they can be joinned. I was able to find related tables with sp_fkeys, but this does not work all the time, and does not work at all with views. I also played with the information_schema but got mixed up and confused quickly.

Any help would be greatly appreciated.

Can you explain what you are trying to do with joining tables dynamically? What is the problem you are trying to solve? It seems very complicated to me. You can use sp_fkeys or INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS or sys.foreign_keys (SQL2005) to get the list of foreign keys and go from there. This will be accurate as long as you have the necessary constraints defined in your schema.

Views do not explicitly have any relationship to tables. You can use WITH SCHEMABINDING option with views but it is different than a foreign key reference. You can use INFORMATION_SCHEMA.VIEW_TABLE_USAGE to get tables that are referenced in a view. There is similar view to get the column usage information.

|||

Umachandar,

Thanks a bunch for the pointers. I am buildinng a reporting application, where users can select columns from a list of tables and views. Naturally, I only want to show tables and views that are related. For example, I know I can join table Orders and view [Order Details Extended], therefore I want to show the two datasources. My problem is that I want to show the relationship dynamically.

I am a little confused with INFORMATION_SCHEMA.VIEW_TABLE_USAGE:

When I run this in Query analyzer, I get a bunch or records, including:

Northwind |dbo |Category Sales for 1997| Northwind |dbo |Product Sales for 1997|

The last column, which is the Table_Name, is [Product Sales for 1997] which is a view! What does this mean? How do I get a list of all tables related to a view? Or vice versa?

Thanks again,

Amine

|||

The usual approach that I have seen taken with this is to come up with your own table that holds the relationship information.

The users start off by selecting one main table to query then as they add data elements from other tables the query builder retrieves the appropriate row and adds that to the query it is building.

MainTable Related Table Relationship

Customers Orders Customers.CustomerID = Sales.CustomerID

Query is bult as follows

SELECT -- whatever columns you select

FROM Customers, Orders

WHERE Customers.CustomerID = Sales.CustomerID

Even though this is not ANSI standard syntax (and I hate seeing joins like this) it is easier to build the string dynamically. If another table is selected then they simply add that to the FROM clause " , CustomerPhone" and to the where clause " AND CustomerPhone.CustomerID = Sales.CustomerID"

|||

Views can reference views or tables. The information schema view goes only one level so you will have to write a recursive query like below to get all dependencies:

with vt
as (
select VIEW_SCHEMA, VIEW_NAME, TABLE_SCHEMA, TABLE_NAME
from INFORMATION_SCHEMA.VIEW_TABLE_USAGE
where objectproperty(object_id(quotename(TABLE_SCHEMA) + N'.' + quotename(TABLE_NAME)), 'IsView') = 1
union all
select t2.VIEW_SCHEMA, t2.VIEW_NAME, t1.TABLE_SCHEMA, t1.TABLE_NAME
from INFORMATION_SCHEMA.VIEW_TABLE_USAGE as t1
join vt as t2
on t1.VIEW_SCHEMA = t2.TABLE_SCHEMA and t1.VIEW_NAME = t2.TABLE_NAME
)
select VIEW_SCHEMA, VIEW_NAME, TABLE_SCHEMA, TABLE_NAME
from vt
union all
select VIEW_SCHEMA, VIEW_NAME, TABLE_SCHEMA, TABLE_NAME
from INFORMATION_SCHEMA.VIEW_TABLE_USAGE
where objectproperty(object_id(quotename(TABLE_SCHEMA) + N'.' + quotename(TABLE_NAME)), 'IsView') = 0
order by VIEW_NAME, TABLE_NAME;

This query is harder to write in SQL Server 2000 but if you know the depth of the dependencies before hand then you can write a single query. Otherwise you can create a multi-statement TVF that simulates above logic.

|||

David,

Thanks for the help. I was trying to avoid doing that. I wanted to only show the tables and views that were related at design time. I guess I could do the "blind" join and if this fails, notify the user.

Thanks

Amine

|||

Awsome. This will get me going.

Thanks a lot!

|||

Hi Again,

I ran this query in SQL 2005 and it worked OK, but does not run in MSDE or SQL 2000. Any ideas why? I get

Msg 156, Level 15, State 1, Line 1

Incorrect syntax near the keyword 'with'.

|||The query put forth by one of the other illustrious posters uses a Common Table Expression (CTE) which is a new feature for SQL 2005. He does mention that for SQL 2000 you will need a different plan of attack.|||

Hi

Try it in SQL 2000

select t2.VIEW_SCHEMA, t2.VIEW_NAME, t1.TABLE_SCHEMA, t1.TABLE_NAME
from
(select VIEW_SCHEMA, VIEW_NAME, TABLE_SCHEMA, TABLE_NAME
from INFORMATION_SCHEMA.VIEW_TABLE_USAGE
where objectproperty(object_id(quotename(TABLE_SCHEMA) + N'.' + quotename(TABLE_NAME)), 'IsView') = 1) t2 inner join
INFORMATION_SCHEMA.VIEW_TABLE_USAGE as t1
on t1.VIEW_SCHEMA = t2.TABLE_SCHEMA and t1.VIEW_NAME = t2.TABLE_NAME

Irfan