SQL Script to find matching column names in tables

Let's say you want to create a join between two tables, you think they have some matching column names that would be good candidates for the WHERE clause, and you do not want to scroll up and down through SQL Server Management System and scribble notes.  Here is an easy way to do this using the INTERSECT keyword and two sys tables (SQL Server 2005 and above only.)



select b.name
from sys.tables a, sys.columns b
where a.name = 'EXTRACT_DIM_COURSE'
and a.object_id = b.object_id

INTERSECT 

select b.name
from sys.tables a, sys.columns b
where a.name = 'EXTRACT_FCT_COURSE_GRADE'
and a.object_id = b.object_id

INTERSECT gives you the results that are part of both result sets.  In this case the result was:

name

DATE_STAMP

DIM_DISTRICT_DISTRICT_ID

DIM_DISTRICT_SCHOOL_YEAR

DIM_SCHOOL_SCHOOL_ID

SCHOOL_YEAR

A more verbose but less readable way of getting the same result is by using a subselect in the where clause:

select b.name

from sys.tables a, sys.columns b

where a.name = 'EXTRACT_FCT_COURSE_GRADE'

and a.object_id = b.object_id

and b.name in (select b.name from sys.tables a, sys.columns b where a.name = 'EXTRACT_DIM_COURSE' and a.object_id = b.object_id)