Using the all_columns System View in SQL Server

As information technology professionals, we're expected to remember a great deal of things: the syntax for 3,000 types of commands (give or take), best practice rules for thousands of scenarios, where we put our car keys, and what all of our children's names are—just to name a few. Familiarizing ourselves with every database's schema we're responsible for is a laudable goal, but it certainly isn't a realistic one. Thankfully, we have the little-know all_columns system view.

The Script

The base script below allows you to search for all columns in a given database where you're aware of the column name, but not necessarily the table it is part of. I've created the base script as a template (see my previous articles about what great fun those are), so there is a single template parameter in the script; Ctrl+Shift+M will bring up a modal window allowing you to swap out the default value for the column you're searching with the one you actually want. 

/******************************************************************************************************Column Lookup TemplateCreated by Tim Ford aka SQLAgentManhttp://thesqlagentman.com and http://sqlcruise.comAs always test in your environment before releasing into the wild in production. This version is configured to run from the master database but can easily be altered to run from a dedicated administrative database used in your environment. Enjoy! Perhaps we'll meet on a future SQL Cruise!******************************************************************************************************DIRECTIONS:  Replace template parameter by using on of the following methods:1. Control + Shift + M 2. QuerySpecify Values for Template Parameters from SSMS MenuHit F5 and enjoy!*/SELECT OBJECT_SCHEMA_NAME(TB.object_id) AS schema__name, TB.name AS object__name, AC.name AS column__name, TB.is_ms_shipped AS is_system_object, type_desc AS object_type , TY.name AS data__type, TY.is_nullable, TY.max_length, TY.precision, TY.scale, TY.collation_nameFROM sys.all_columns AC LEFT JOIN sys.tables TB ON AC.object_id = TB.object_idINNER JOIN sys.types TY ON AC.system_type_id = TY.system_type_idWHERE AC.name = ''ORDER BY 1,2

Alternatives

If you don't have the full picture when it comes to what you're looking for, swap out the following line of code with the one in the WHERE clause above. Keep in mind though, that using a search predicate where you have a leading '%' character, may lead to a scan versus a seek (and the overhead that comes with it), but this system view is normally going to be small enough that a scan would be chosen over a seek even if your code was Brad Pitt meets Kate Upton perfect:

WHERE AC.name LIKE '%%'

Bread Crumbs

In the end, this may not get you where you want to go, but it does show you the trail of bread crumbs that should lead you to what you're looking for.