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

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