How to find Relationship between Tables ?

Question

How to find Relationship between Tables ?

aloshya

Ten Centuries

Points: 1105
More actions
October 9, 2017 at 4:34 pm

#339809

Hello
I have an SQL database with more than 500 tables.
How to find the relationship between tables and the associated column that makes the relationship between tab?es
select * from sys.dm_sql_referenced_entities ('[dbo].[ALARM]','object')
The above query didn't list any relationship although I have many tables which is pointing to alarm table?
Regards

Viewing 15 posts - 1 through 15 (of 17 total)

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

Sue_H SSC Guru Points: 90860 More actions · Answer 1

aloshya - Monday, October 9, 2017 4:34 PM
Hello
I have an SQL database with more than 500 tables.
How to find the relationship between tables and the associated column that makes the relationship between tab?es
select * from sys.dm_sql_referenced_entities ('[dbo].[ALARM]','object')
The above query didn't list any relationship although I have many tables which is pointing to alarm table?
Regards

dm_sql_referenced_entities would be used more for dependencies. For relationships between tables, sys.foreign_keys can probably give you the information you are looking for.
See if something like this works for you:

SELECT fk.name AS ForeignKey, OBJECT_NAME(fk.parent_object_id) AS ParentTable, COL_NAME(fc.parent_object_id, fc.parent_column_id) AS ParentColumn, OBJECT_NAME (fk.referenced_object_id) AS ReferencedTable, COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS ReferenceColumn FROM sys.foreign_keys AS fk INNER JOIN sys.foreign_key_columns AS fc ON fk.OBJECT_ID = fc.constraint_object_id ORDER BY ParentTable, ReferencedTable

Sue

aloshya Ten Centuries Points: 1105 More actions · Answer 2

Sue_H - Monday, October 9, 2017 5:55 PM
aloshya - Monday, October 9, 2017 4:34 PM
Hello
I have an SQL database with more than 500 tables.
How to find the relationship between tables and the associated column that makes the relationship between tab?es
select * from sys.dm_sql_referenced_entities ('[dbo].[ALARM]','object')
The above query didn't list any relationship although I have many tables which is pointing to alarm table?
Regards
dm_sql_referenced_entities would be used more for dependencies. For relationships between tables, sys.foreign_keys can probably give you the information you are looking for.
See if something like this works for you:
SELECT fk.name AS ForeignKey, OBJECT_NAME(fk.parent_object_id) AS ParentTable, COL_NAME(fc.parent_object_id, fc.parent_column_id) AS ParentColumn, OBJECT_NAME (fk.referenced_object_id) AS ReferencedTable, COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS ReferenceColumn FROM sys.foreign_keys AS fk INNER JOIN sys.foreign_key_columns AS fc ON fk.OBJECT_ID = fc.constraint_object_id ORDER BY ParentTable, ReferencedTable
Sue

How to use this query. I executed as same but its not returning anything

Umer Akram SSC-Insane Points: 22209 More actions · Answer 3

aloshya - Monday, October 9, 2017 10:59 PM
Sue_H - Monday, October 9, 2017 5:55 PM
aloshya - Monday, October 9, 2017 4:34 PM
Hello
I have an SQL database with more than 500 tables.
How to find the relationship between tables and the associated column that makes the relationship between tab?es
select * from sys.dm_sql_referenced_entities ('[dbo].[ALARM]','object')
The above query didn't list any relationship although I have many tables which is pointing to alarm table?
Regards
dm_sql_referenced_entities would be used more for dependencies. For relationships between tables, sys.foreign_keys can probably give you the information you are looking for.
See if something like this works for you:
SELECT fk.name AS ForeignKey, OBJECT_NAME(fk.parent_object_id) AS ParentTable, COL_NAME(fc.parent_object_id, fc.parent_column_id) AS ParentColumn, OBJECT_NAME (fk.referenced_object_id) AS ReferencedTable, COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS ReferenceColumn FROM sys.foreign_keys AS fk INNER JOIN sys.foreign_key_columns AS fc ON fk.OBJECT_ID = fc.constraint_object_id ORDER BY ParentTable, ReferencedTable
Sue
How to use this query. I executed as same but its not returning anything

The thing is, This query will result set IF AND ONLY IF There is a FOREIGN KEY defined in the database. For better understanding check the following example:
USE tempdb GO

---- Table Creation Create table tblParent ( Id int, ColA varchar(10), CONSTRAINT [PK_tblParent_Id] PRIMARY KEY CLUSTERED (Id ASC) ) ;

Create table tblChild ( Id int, ParentId int, ColA varchar(10), Constraint fk_tblChild_ParentID FOREIGN KEY (ParentId) REFERENCES tblParent (Id) ) ;

----- Relationship Query SELECT fk.name AS ForeignKey, OBJECT_NAME(fk.parent_object_id) AS ParentTable, COL_NAME(fc.parent_object_id, fc.parent_column_id) AS ParentColumn, OBJECT_NAME (fk.referenced_object_id) AS ReferencedTable, COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS ReferenceColumn FROM sys.foreign_keys AS fk INNER JOIN sys.foreign_key_columns AS fc ON fk.OBJECT_ID = fc.constraint_object_id ORDER BY ParentTable, ReferencedTable ; --- Clean Up IF OBJECT_ID('tblChild') IS NOT NULL DROP TABLE tblChild; IF OBJECT_ID('tblParent') IS NOT NULL DROP TABLE tblParent;

After execution you will get the following result

ForeignKey ParentTable ParentColumn ReferencedTable ReferenceColumn fk_tblChild_ParentID tblChild ParentId tblParent Id
Hope it helps.

aloshya Ten Centuries Points: 1105 More actions · Answer 4

twin.devil - Tuesday, October 10, 2017 12:00 AM
aloshya - Monday, October 9, 2017 10:59 PM
Sue_H - Monday, October 9, 2017 5:55 PM
aloshya - Monday, October 9, 2017 4:34 PM
Hello
I have an SQL database with more than 500 tables.
How to find the relationship between tables and the associated column that makes the relationship between tab?es
  select * from     sys.dm_sql_referenced_entities ('[dbo].[ALARM]','object')
The above query didn't list any relationship although I have many tables which is pointing to alarm table?
Regards
dm_sql_referenced_entities would be used more for dependencies. For relationships between tables, sys.foreign_keys can probably give you the information you are looking for.
See if something like this works for you:
SELECT     fk.name AS ForeignKey,     OBJECT_NAME(fk.parent_object_id) AS ParentTable,     COL_NAME(fc.parent_object_id, fc.parent_column_id) AS ParentColumn,     OBJECT_NAME (fk.referenced_object_id) AS ReferencedTable,     COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS ReferenceColumn FROM sys.foreign_keys AS fk INNER JOIN sys.foreign_key_columns AS fc ON fk.OBJECT_ID = fc.constraint_object_id ORDER BY ParentTable, ReferencedTable
Sue
How to use this query. I executed as same but its not returning anything
The thing is, This query will result set IF AND ONLY IF There is a FOREIGN KEY defined in the database. For better understanding check the following example:
USE tempdb GO
---- Table Creation Create table tblParent (     Id int,     ColA varchar(10), CONSTRAINT [PK_tblParent_Id] PRIMARY KEY CLUSTERED (Id ASC) ) ;
Create table tblChild (     Id int,     ParentId int,     ColA varchar(10),     Constraint fk_tblChild_ParentID FOREIGN KEY (ParentId) REFERENCES tblParent (Id) ) ;
----- Relationship Query SELECT fk.name AS ForeignKey, OBJECT_NAME(fk.parent_object_id) AS ParentTable, COL_NAME(fc.parent_object_id, fc.parent_column_id) AS ParentColumn, OBJECT_NAME (fk.referenced_object_id) AS ReferencedTable, COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS ReferenceColumn FROM sys.foreign_keys AS fk INNER JOIN sys.foreign_key_columns AS fc ON fk.OBJECT_ID = fc.constraint_object_id ORDER BY ParentTable, ReferencedTable ; --- Clean Up IF OBJECT_ID('tblChild') IS NOT NULL DROP TABLE tblChild; IF OBJECT_ID('tblParent') IS NOT NULL DROP TABLE tblParent;
After execution you will get the following result
ForeignKey    ParentTable    ParentColumn    ReferencedTable    ReferenceColumn fk_tblChild_ParentID    tblChild    ParentId    tblParent    Id
Hope it helps.

i dont have foreign key relationship but still there is relationship between tables in one way or another

Umer Akram SSC-Insane Points: 22209 More actions · Answer 5

aloshya - Tuesday, October 10, 2017 12:58 AM
i dont have foreign key relationship but still there is relationship between tables in one way or another

Well Data dictionaries are very handy if there is any it would have help you a great deal in this scenario. The reason is none of the reference related queries will help you if there is no foreign keys in your database.
You will have to manually identify these relationships mainly through Application () which is using this db and some information from SPs/Views. If you have any senior to consult with that would help too.

BTW, Now may be the right time for a data dictionary.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 6

twin.devil - Tuesday, October 10, 2017 1:11 AM
BTW, Now may be the right time for a data dictionary.

Yes, and why not add the foreign key constraints in as well? It improves performance and data integrity and makes the database self-documenting.

Does your database even have primary key constraints?

John

Phil Parkin SSC Guru Points: 246965 More actions · Answer 7

aloshya - Tuesday, October 10, 2017 12:58 AM
i dont have foreign key relationship but still there is relationship between tables in one way or another

How is this relationship defined? 'One way or another' is not a clear definition.
If relationships are not implemented physically, via foreign keys, why did you have any expectation that they would somehow be captured in DMVs or system tables?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

aloshya Ten Centuries Points: 1105 More actions · Answer 8

twin.devil - Tuesday, October 10, 2017 1:11 AM
aloshya - Tuesday, October 10, 2017 12:58 AM
i dont have foreign key relationship but still there is relationship between tables in one way or another
Well Data dictionaries are very handy if there is any it would have help you a great deal in this scenario. The reason is none of the reference related queries will help you if there is no foreign keys in your database.
You will have to manually identify these relationships mainly through Application () which is using this db and some information from SPs/Views. If you have any senior to consult with that would help too.
BTW, Now may be the right time for a data dictionary.

I entered some values through application which is connected to the db and checked in query plan cache to see which tables are being used. still i am not reaching where i really wants

aloshya Ten Centuries Points: 1105 More actions · Answer 9

John Mitchell-245523 - Tuesday, October 10, 2017 1:41 AM
twin.devil - Tuesday, October 10, 2017 1:11 AM
BTW, Now may be the right time for a data dictionary.
Yes, and why not add the foreign key constraints in as well? It improves performance and data integrity and makes the database self-documenting.
Does your database even have primary key constraints?
John

Primary constraints are there

example

For dbo.alarm and dbo.alarmdevice, both have their own primary key called panelid and they are denoting the same

Sue_H SSC Guru Points: 90860 More actions · Answer 10

aloshya - Tuesday, October 10, 2017 3:48 PM
John Mitchell-245523 - Tuesday, October 10, 2017 1:41 AM
twin.devil - Tuesday, October 10, 2017 1:11 AM
BTW, Now may be the right time for a data dictionary.
Yes, and why not add the foreign key constraints in as well? It improves performance and data integrity and makes the database self-documenting.
Does your database even have primary key constraints?
John
Primary constraints are there
example
For dbo.alarm and dbo.alarmdevice, both have their own primary key called panelid and they are denoting the same

What is being said is that there are no relationships for the alarm table. There would be foreign keys if there were relationships with other tables. So not having any isn't a good thing.
The only other thing I can guess at would be that you aren't asking about relationships but if something calls the alarm table. That would be a dependency which is what you were trying in your first post. But you would use a different dynamic management function. So try this (it's slightly different from your first one) and see if you get any results:
select * from sys.dm_sql_referencing_entities ('[dbo].[ALARM]','object')

If you still get nothing then it would seem the application uses ad hoc queries.

Sue

aloshya Ten Centuries Points: 1105 More actions · Answer 11

Phil Parkin - Tuesday, October 10, 2017 6:13 AM
aloshya - Tuesday, October 10, 2017 12:58 AM
i dont have foreign key relationship but still there is relationship between tables in one way or another
How is this relationship defined? 'One way or another' is not a clear definition.
If relationships are not implemented physically, via foreign keys, why did you have any expectation that they would somehow be captured in DMVs or system tables?

See this article
https://solutioncenter.apexsql.com/what-is-change-tracking-and-how-to-set-it-up/

aloshya Ten Centuries Points: 1105 More actions · Answer 12

Sue_H - Tuesday, October 10, 2017 4:00 PM
aloshya - Tuesday, October 10, 2017 3:48 PM
John Mitchell-245523 - Tuesday, October 10, 2017 1:41 AM
twin.devil - Tuesday, October 10, 2017 1:11 AM
BTW, Now may be the right time for a data dictionary.
Yes, and why not add the foreign key constraints in as well? It improves performance and data integrity and makes the database self-documenting.
Does your database even have primary key constraints?
John
Primary constraints are there
example
For dbo.alarm and dbo.alarmdevice, both have their own primary key called panelid and they are denoting the same
What is being said is that there are no relationships for the alarm table. There would be foreign keys if there were relationships with other tables. So not having any isn't a good thing.
The only other thing I can guess at would be that you aren't asking about relationships but if something calls the alarm table. That would be a dependency which is what you were trying in your first post. But you would use a different dynamic management function. So try this (it's slightly different from your first one) and see if you get any results:
select * from sys.dm_sql_referencing_entities ('[dbo].[ALARM]','object')
If you still get nothing then it would seem the application uses ad hoc queries.
Sue

It returns the above

Sue_H SSC Guru Points: 90860 More actions · Answer 13

aloshya - Tuesday, October 10, 2017 4:08 PM
Sue_H - Tuesday, October 10, 2017 4:00 PM
aloshya - Tuesday, October 10, 2017 3:48 PM
John Mitchell-245523 - Tuesday, October 10, 2017 1:41 AM
twin.devil - Tuesday, October 10, 2017 1:11 AM
BTW, Now may be the right time for a data dictionary.
Yes, and why not add the foreign key constraints in as well? It improves performance and data integrity and makes the database self-documenting.
Does your database even have primary key constraints?
John
Primary constraints are there
example
For dbo.alarm and dbo.alarmdevice, both have their own primary key called panelid and they are denoting the same
What is being said is that there are no relationships for the alarm table. There would be foreign keys if there were relationships with other tables. So not having any isn't a good thing.
The only other thing I can guess at would be that you aren't asking about relationships but if something calls the alarm table. That would be a dependency which is what you were trying in your first post. But you would use a different dynamic management function. So try this (it's slightly different from your first one) and see if you get any results:
select * from sys.dm_sql_referencing_entities ('[dbo].[ALARM]','object')
If you still get nothing then it would seem the application uses ad hoc queries.
Sue
It returns the above

So that is the only thing referencing the alarm table. There are no relationships between the tables and one object references the Alarm table.
If a lot of things are referencing that table, it isn't defined in the database. You need to search through the application code and figure it out from there as it is not in the database.

Sue

aloshya Ten Centuries Points: 1105 More actions · Answer 14

Sue_H - Tuesday, October 10, 2017 4:32 PM
aloshya - Tuesday, October 10, 2017 4:08 PM
Sue_H - Tuesday, October 10, 2017 4:00 PM
aloshya - Tuesday, October 10, 2017 3:48 PM
John Mitchell-245523 - Tuesday, October 10, 2017 1:41 AM
twin.devil - Tuesday, October 10, 2017 1:11 AM
BTW, Now may be the right time for a data dictionary.
Yes, and why not add the foreign key constraints in as well? It improves performance and data integrity and makes the database self-documenting.
Does your database even have primary key constraints?
John
Primary constraints are there
example
For dbo.alarm and dbo.alarmdevice, both have their own primary key called panelid and they are denoting the same
What is being said is that there are no relationships for the alarm table. There would be foreign keys if there were relationships with other tables. So not having any isn't a good thing.
The only other thing I can guess at would be that you aren't asking about relationships but if something calls the alarm table. That would be a dependency which is what you were trying in your first post. But you would use a different dynamic management function. So try this (it's slightly different from your first one) and see if you get any results:
select * from sys.dm_sql_referencing_entities ('[dbo].[ALARM]','object')
If you still get nothing then it would seem the application uses ad hoc queries.
Sue
It returns the above
So that is the only thing referencing the alarm table. There are no relationships between the tables and one object references the Alarm table.
If a lot of things are referencing that table, it isn't defined in the database. You need to search through the application code and figure it out from there as it is not in the database.
Sue

what does the above relationship means and what is referencing entity here referring to . ?