Identifying Relationship Between Columns In SQL

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 

  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 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 

  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.

Output