Saturday, August 11, 2012

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)