Oftentimes, I am presented with queries from a client with a myriad of joins that have no table aliases. In order to improve performance, I often will have to create temporary tables from pieces of the query, and sometimes they need to be created manually as opposed to performing a SELECT INTO.  Having to search through all of the tables through the GUI manually to determine the proper information on the columns can be quite a pain and a waste of time.  In an effort to better utilize my time, I created a simple query that will return where the column resides as well as everything you need to know about the column and more.

Below is an example run against the AdventureWorks2012 database returning data for any columns having the name of “EmailAddress.”

SELECTs.[name]'Schema_Name'
                 ,t.[name]'Table_Name'
                 ,c.[name]'Column_Name'
                 ,d.[name]'Data_Type'
                 ,d.[max_length]'Max_Length'
                 ,d.[precision]'Precision'
                 ,c.[is_identity]'Is_Identity'
                 ,c.[is_nullable]'Is_Nullable'
                 ,t.[create_date]'Date_Created'
                 ,t.[modify_date]'Date_Modified'
FROMsys.schemas s
INNER JOINsys.tables t ONs.schema_id= t.schema_id
INNER JOINsys.columns c ONt.object_id= c.object_id
INNER JOINsys.types d ONc.user_type_id = d.user_type_id
WHEREc.NAME ='EmailAddress'

You can also utilize an IN as opposed to an equal and return all of the necessary columns from one query, as shown in the below example run against the AdventureWorks2012 database.

SELECTs.[name]'Schema_Name'
                 ,t.[name]'Table_Name'
                 ,c.[name]'Column_Name'
                 ,d.[name]'Data_Type'
                 ,d.[max_length]'Max_Length'
                 ,d.[precision]'Precision'
                 ,c.[is_identity]'Is_Identity'
                 ,c.[is_nullable]'Is_Nullable'
                 ,t.[create_date]'Date_Created'
                 ,t.[modify_date]'Date_Modified'
FROMsys.schemas s
INNER JOINsys.tables tONs.schema_id=t.schema_id
INNER JOINsys.columns cONt.object_id=c.object_id
INNER JOINsys.types dONc.user_type_id=d.user_type_id
WHEREc.NAME IN(              
                                    'Name'              
                                      ,'AddressLine1'             
                                      ,'AddressLine2'              
                                      ,'City'              
                                     ,'State'              
                                      ,'EmailAddress'              
                                     )

I hope that this quick tip can help others utilize their time more effectively while making SQL queries run more efficiently!  Thanks for reading!