Query that shows similar columns between different tables on a database 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 &lt;&gt; 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.
A common-table-expression (See CTE) will help you here quite nicely..

with columns as (
    select C.COLUMN_NAME,
           C.TABLE_NAME
    from INFORMATION_SCHEMA.COLUMNS C
    inner join INFORMATION_SCHEMA.TABLES T on T.TABLE_NAME = C.TABLE_NAME
    where T.TABLE_TYPE = 'BASE TABLE')
select C.COLUMN_NAME as 'Similar Column',
       C.TABLE_NAME as 'First Table',
       D.TABLE_NAME as 'Second Table'
from columns C
inner join columns D
   on C.COLUMN_NAME = D.COLUMN_NAME
  and C.TABLE_NAME &lt; D.TABLE_NAME
order by C.TABLE_NAME, D.TABLE_NAME, C.COLUMN_NAME


The above query reduces the complexity of the column/table table joins, and reuses the result. Further, by using &lt; instead of &lt;&gt; on the table-name anti-join, it removes your duplicates.