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

  1. drop table #TempAssociation  
  2. SELECT  
  3. f.name AS ForeignKey,  
  4. SCHEMA_NAME(f.SCHEMA_ID) SchemaName,  
  5. OBJECT_NAME(f.parent_object_id) AS TableName,  
  6. COL_NAME(  
  7. fc.parent_object_id, fc.parent_column_id  
  8. AS ColumnName,  
  9. SCHEMA_NAME(o.SCHEMA_ID) ReferenceSchemaName,  
  10. OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,  
  11. COL_NAME(  
  12. fc.referenced_object_id, fc.referenced_column_id  
  13. AS ReferenceColumnName into #TempAssociation  
  14. FROM  
  15. sys.foreign_keys AS f  
  16. INNER JOIN sys.foreign_key_columns AS fc  
  17. INNER JOIN sys.objects AS o ON o.OBJECT_ID = fc.referenced_object_id ON f.OBJECT_ID = fc.constraint_object_id  
  18. select  
  19. *  
  20. from  
  21. #TempAssociation  
  22. where  
  23. ColumnName like ‘ %< First Column to Search >% ’  
  24. or ColumnName like ‘ %< Second Column to Search >% ’  
  25. drop  
  26. table #TempAssociation   

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

find relation between columns

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 

  1. --Search Database Column by name  
  2. SELECT  
  3. table_name = sysobjects.name,  
  4. column_name = syscolumns.name,  
  5. datatype = systypes.name,  
  6. length = syscolumns.length  
  7. FROM  
  8. sysobjects  
  9. JOIN syscolumns ON sysobjects.id = syscolumns.id  
  10. JOIN systypes ON syscolumns.xtype = systypes.xtype  
  11. WHERE  
  12. syscolumns.name LIKE '%<Your Search Column Name>%'   

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

query to search column in database