How can I determine relationships or dependencies between tables or queries?

Question

How can I determine relationships or dependencies between tables or queries?

frank.kress

SSC Veteran

Points: 281
More actions
May 8, 2020 at 10:23 pm

#3750082

Hello everybody
I have inherited a very large database with really many tables, queries and countless stored procedures (SP).
There is also no ER model created.
Tables and queries are used in SP and are related to each other.
And now my question:
How can I determine relationships or dependencies between tables or queries?
Or maybe there is a tool that can create an ER model for me?
I want to find out which objects (table or view) are related and where they are used (depending).
Can someone give me a tip here?

Viewing 9 posts - 1 through 8 (of 8 total)

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

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 1

Do you know if any FK's have been created? You can do a quick check with the following code to see if any are present. We can, of course, expand on that if there are any.

 SELECT * FROM sys.foreign_key_columns;

We can also do a check on "dependencies" but those are a bit more difficult to resolve. Try the FK route first.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

frank.kress SSC Veteran Points: 281 More actions · Answer 2

Hi Jeff fashions

thank you for your quick reply.

But unfortunately there is no one FK in this database 🙁

Only fields were joined together. These fields have no index and are not declared as FKs.

And that's my problem.

To understand which tables / views are related to each other, I have to look at each individual view and SP.

Well, I can do that. Then I'm busy for a few years. Ok ok, some months busy.

But I don't think I have to do that.

When I create a view like this example:

CREATE VIEW dbo.v_Sample_View
AS
    SELECT  t1.*, t2.Column2
    FROM    dbo.Sample_Table1            t1
            INNER JOIN dbo.Sample_Table2 t2
               ON t2.column_1 = t1.column_1;

GO

The SQL Server stores the entities dbo.Sample_Table1 and dbo.Sample_Table2 and also the relationship INNER JOIN to the fields somewhere. Or?

There must be a way to read this information. But where?

With this information I would then like to create a kind of ER-Model (crow's foot notation).

I look forward to any kind of suggestions.

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 3

I don't have any code of my own to do what you need (I actually do use FKs and so don't need such code) but the following article has a pretty good discussion with examples of the tools you might use to build one.

https://www.sqlshack.com/how-to-create-a-sql-dependency-diagram-in-sql-server/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 4

Here are another couple of system views that might help...

sys.sql_dependencies

sys.sql_expression_dependencies

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 5

And one more link by a fellow that I have a strong trust in:

https://www.red-gate.com/simple-talk/sql/t-sql-programming/dependencies-and-references-in-sql-server/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

frank.kress SSC Veteran Points: 281 More actions · Answer 6

Hi Jeff

that's incredibly fantastic. That's exactly what I thought.

The system views such as: sys.sql_dependencies goes in the right direction but the tool from Quest is a real hammer.

And the link https://www.sqlshack.com/how-to-create-a-sql-dependency-diagram-in-sql-server/ is very interesting.

Thanks Jeff, have an answer to my question. Now I have to take a closer look at it.

Greetings from Germany, specifically from Frankfurt am Main

Frank

Jim Couper SSC Enthusiast Points: 185 More actions · Answer 7

Redgate SQL Dependency Tracker can help you with this.

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 8

frank.kress wrote:

Hi Jeff
that's incredibly fantastic. That's exactly what I thought.
The system views such as: sys.sql_dependencies goes in the right direction but the tool from Quest is a real hammer.
And the link https://www.sqlshack.com/how-to-create-a-sql-dependency-diagram-in-sql-server/ is very interesting.
Thanks Jeff, have an answer to my question. Now I have to take a closer look at it.
Greetings from Germany, specifically from Frankfurt am Main
Frank

Glad it helped. Thank you for the feedback, Frank.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)