T-SQL Query- Search for Column Details by Name

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.”

SELECT s.[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’
FROM sys.schemas s
INNER JOIN sys.tables t ON s.schema_id = t.schema_id
INNER JOIN sys.columns c ON t.object_id = c.object_id
INNER JOIN sys.types d ON c.user_type_id = d.user_type_id
WHERE c.NAME = ‘EmailAddress’

BWineLand_BlogPost1_Pic1.png

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.

SELECT s.[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’
FROM sys.schemas s
INNER JOIN sys.tables t ON s.schema_id = t.schema_id
INNER JOIN sys.columns c ON t.object_id = c.object_id
INNER JOIN sys.types d ON c.user_type_id = d.user_type_id
WHERE c.NAME IN (              
                                    ‘Name’              
                                      ,‘AddressLine1’             
                                      ,‘AddressLine2’              
                                      ,‘City’              
                                      ,‘State’              
                                      ,‘EmailAddress’              
                                      )

BWineland_BlogPost2_Pic2.png

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

Leave a Reply

Your email address will not be published. Required fields are marked *