In my work, I often have to take a naive look at tables and then never see them again, and it can be difficult to see what lines up where for large databases with many columns. I wrote this, and while it works, it seems to require an unnecessary number of joins. I'm wondering if there is a better way to handle this issue or a way to cut down the code.
select A.COLUMN_NAME as 'Similar Column', y.TABLE_NAME as 'First Table', z.TABLE_NAME as 'Second Table' from INFORMATION_SCHEMA.COLUMNS A full outer join INFORMATION_SCHEMA.COLUMNS B on A.COLUMN_NAME = B.COLUMN_NAME cross apply ( SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE') as y cross apply ( SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE') as z where y.TABLE_NAME <> z.TABLE_NAME and A.TABLE_NAME = y.TABLE_NAME and B.TABLE_NAME = z.TABLE_NAME order by y.TABLE_NAME,z.TABLE_NAME, a.COLUMN_NAME
I also wonder if there is a way to prevent duplicate entries, in other words, Activity, RMA is the same as RMA, Activity.