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 

--Search Database Column by name
SELECT
table_name = sysobjects.name,
column_name = syscolumns.name,
datatype = systypes.name,
length = syscolumns.length
FROM
sysobjects
JOIN syscolumns ON sysobjects.id = syscolumns.id
JOIN systypes ON syscolumns.xtype = systypes.xtype
WHERE
syscolumns.name LIKE '%<Your Search Column Name>%'

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