Find connections between sql tables

Is there a way the computer can show me how to get from table A to table F, so I don't have to manually figure that out?

For example, say I have a database which has a table of Office Chairs, and another table of Driver Safety Records, and I want to know how to get from office chair to drivers licence, because some research statistician wants to see if there's a correlation between office chair type and driving records.

And let's say all the table columns are in German.

I could figure it out if I searched enough, but it would be so much nicer if the computer would just connect the dots for me.

(e.g. office chair → office → employee → person → driver's licence → safety records)
deleydSoftware EngineerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Walter RitzelSenior Software EngineerCommented:
If the database have all the foreign keys, you just need to look that info. depending on the database, it is a simple query to figure that out.
dsackerContract ERP Admin/ConsultantCommented:
Hi deleyd. I don't think you're gonna find something that will do this without putting your own eyes on it. Check the foreign keys.

You could write a script to find every column in a table that is in another table, then recurse it through the other tables, but by the time you're done, you may have far more to look through than you bargained for.

On this one, it pays to simply get seasoned with your tables and your data. It seems in the very example you cited, you already knew beforehand what types of relationships you were seeking from one table to the next.

Sorry that I don't have a "rabbit in the hat" on this one, but I think the best solution is simply to get to know the data and the structures.

However. :)

If you DO want a script that can located the columns in your starting table, where those columns are in other tables, use this:

SELECT  t1.table_name,
        t1.column_name,
        t2.table_name   AS [Found In]
FROM    information_schema.columns t1
JOIN    information_schema.columns t2
ON      t2.column_name = t1.column_name
AND     t2.table_name <> t1.table_name
WHERE   t1.table_name = 'YourStartingTable'
AND     t1.column_name IN ('maybe', 'just', 'a', 'few', 'columns')  -- comment out if searching all columns
ORDER BY t1.table_name,
         t1.column_name,
         t2.table_name

Open in new window

dsackerContract ERP Admin/ConsultantCommented:
In addition, here is a script that will check foreign-key relationships for you:

SET NOCOUNT ON
DECLARE @TableName  varchar(40),
        @Max        varchar(5),
        @Sql        varchar(255)

SET @TableName = 'whse'
IF SUBSTRING('$(table)', 3, 5) <> 'table'
    SET @TableName = '$(table)'

IF OBJECT_ID('tempdb..#temp_fk_results') IS NOT NULL
    DROP TABLE #temp_fk_results

SELECT  Fk.name                             AS [Constraint],
        object_name(Fk.parent_object_id)    AS FK_table,
        col_name(Fk.parent_object_id, Fk_Cl.parent_column_id)
                                            AS FK_column,
        TbR.name                            AS PK_table,
        col_name(Fk.referenced_object_id, Fk_Cl.referenced_column_id)
                                            AS PK_column
INTO    #temp_fk_results
FROM    sys.foreign_keys Fk
LEFT JOIN sys.tables TbR
ON      TbR.object_id = Fk.referenced_object_id
JOIN    sys.foreign_key_columns Fk_Cl
ON      Fk_Cl.constraint_object_id = Fk.object_id
WHERE	Fk.parent_object_id     = object_id(@TableName)
OR      Fk.referenced_object_id = object_id(@TableName)
ORDER BY CASE
            WHEN object_name(Fk.parent_object_id) = @TableName THEN '0'
            ELSE object_name(Fk.parent_object_id)
         END,
         CONVERT(int,
            CASE
                WHEN ISNUMERIC(REPLACE(Fk.name, object_name(Fk.parent_object_id) + 'FK', '')) = 0 THEN '0'
                ELSE REPLACE(Fk.name, object_name(Fk.parent_object_id) + 'FK', '')
            END),
         CASE
            WHEN object_name(Fk.parent_object_id) = @TableName THEN TbR.name
            ELSE object_name(Fk.parent_object_id)
         END

SELECT * FROM #temp_fk_results
DROP TABLE #temp_fk_results
SET NOCOUNT OFF

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

grendel777Commented:
I know we're not telling you what you want to hear. If the foreign keys are set up, which they often aren't if the database was created by a web developer rather than a database developer, then you can follow the trails around. Expand the table in Management Studio and then expand Keys - they grey ones are foreign keys that "link" the two tables together. A database can run just fine without them being set up, but it makes your job harder and those links have to be managed by some other code like a middle tier. There are some tools that will generate database diagrams for you: SourceForge has one called SchemaSpy (never used it), and I think RedGate has one, too. They will all need those foreign keys set up, but if those are in place the tools can generate nice visual representations so you can see the links.
Vitor MontalvãoMSSQL Senior EngineerCommented:
In SQL Server Management Studio, open the database and click on Diagrams folder. Then right-click on it and chose "New Database Diagram...". Now select the tables you want and if they have relationships it will show you.
deleydSoftware EngineerAuthor Commented:
Thank you everyone. I guess there's no easy simple solution other than either know the database or be good at figuring it out.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.