Identifying Relationship Between Columns In SQL

This article briefs you about identifying the relationship between the columns in SQL.

You might have a question in my mind - If I have two fields, how am I going to find a relationship in between the two columns quickly? Especially, when I have very less understanding of the database.
Well, this can be done without looking into the actual database table and by just executing a query with the proper input and column names at the proper place.

The query is given below to find the relationship between any two columns in a given database.

How to find the relationship between two database columns

–drop table #TempAssociation
SELECT
f.name AS ForeignKey,
SCHEMA_NAME(f.SCHEMA_ID) SchemaName,
OBJECT_NAME(f.parent_object_id) AS TableName,
COL_NAME(
fc.parent_object_id, fc.parent_column_id
) AS ColumnName,
SCHEMA_NAME(o.SCHEMA_ID) ReferenceSchemaName,
OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
COL_NAME(
fc.referenced_object_id, fc.referenced_column_id
) AS ReferenceColumnName into #TempAssociation
FROM
sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc
INNER JOIN sys.objects AS o ON o.OBJECT_ID = fc.referenced_object_id ON f.OBJECT_ID = fc.constraint_object_id
select
*
from
#TempAssociation
where
ColumnName like ‘ %< First Column to Search >% ’
or ColumnName like ‘ %< Second Column to Search >% ’
drop
table #TempAssociation

The output is in the form of a table, as given below.

Use the query given above, where you do not have to always refer towards the database.

How to search the column in the database by its name

The output is in the form of the table given below.