This article briefs you about identifying relationship between the columns in SQL.
- There is always a question in my mind, if I have two fields with me, how am I going to find a relationship in between the two columns quickly, if I have less idea about the database.
- This can be done without looking into the actual database table by just executing a query with the proper input and column names at the proper place.
- Second one is used to search a particular column in the given database
The query is given below to find the relation between any two columns in the given database.
How to find the relationship between the two coloumns
- –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 the table given below.
![Output]()
Use the query given above, where you do not have to refer always towards the database.
How to search the column in the database by its 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.
![Output]()