query to get list of tables that has same columns from information_schema.columns? idea is to list all the tables that has redundant columns across database using information_schema.columns!
let's say 

table1 = dbo.ABC
columns = id, name, department, designation

table2= dbo.DEF
columns= department, permissions, cube 

table3= dbo.GHI
columns= street, zip, city, state

the query should list the tables 'dbo.ABC'  and 'dbo.DEF'
something like this:

select distinct c1.table_schema||'.'||c1.table_name
from information_schema.columns c1
where exists (select 1
              from information_schema.columns c2
              where c2.table_name &lt;&gt; c1.table_name
                and c2.column_name = c1.column_name
                and c2.table_schema = c1.table_schema);