0

I would like to find a way to understand if there is a relationship between two columns present in two different tables.

For example in the table [Sales].[SalesOrderHeader], I have a column SalesOrderID and in another table [Person].[EmailAddress], there is BusinessEntityID.

How can I check to see if there is a table that creates a relationship between these 2 columns? Or how can I be sure that there is not a relationship between these 2 columns?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
ema7606
  • 9
  • 2
  • Have you tried `INFORMATION_SCHEMA.KEY_COLUMN_USAGE`? – Dai Jan 04 '19 at 00:24
  • Are you using any software to view tables? Some software like MS Access allow you to see relationships. – Abu Nooh Jan 04 '19 at 00:28
  • Can you be sure that the designer of the database actually added foreign key constraints? Is there no design information regarding this database available? – Dale K Jan 04 '19 at 00:36
  • 3
    Possible duplicate of [How to find foreign key dependencies in SQL Server?](https://stackoverflow.com/questions/925738/how-to-find-foreign-key-dependencies-in-sql-server) – Vijunav Vastivch Jan 04 '19 at 00:42

3 Answers3

0

INFORMATION_SCHEMA is what you are looking for. You can see whether or not a given column is used in a constraint by executing

SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE WHERE TABLE_NAME = 'Person' AND COLUMN_NAME = 'BusinessEntityID'

You will have to do some additional work to focus on your specific solution, but this is where to start.

competent_tech
  • 44,465
  • 11
  • 90
  • 113
0

You could do one the following to find the tables that reference [Sales].[SalesOrderHeader]:

EXEC sp_fkeys @pktable_name = N'SalesOrderHeader',@pktable_owner = N'Sales';
JMabee
  • 2,230
  • 2
  • 9
  • 13
-1

I apologize before hand for what follows:

    create table #rels (rel_name varchar(max), matches int) declare @sql varchar(max) = '' select @sql+= char(10) + 'insert into #rels select ''' tbla + '.'  + col_a + '.' + tbl_b + '.' col_b ''' colrel, count(*) from ' + tbl_a + ' join ' + tbl_b + '  on cast(' + col_a + ' as varchar(max)) = cast(' + col_b + ' as varchar(max)) from ( select a.column_name col_a, object_name(a.object_id) tbl_a, b.column_name col_b, object_name(b.object_id) tbl_b from sys.columns a cross apply sys.columns b where  a.column_name <> b.column_name where a.system_type_id = b.system_type_id ) cols exec (@sql) select * from #rels where matches > 0 order by matches desc drop table #rels
Dale Ludwig
  • 106
  • 5