Query that shows similar columns between different tables on a database
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.