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.

  1. /*
  2. *****************************************************************************************************
  3. Column Lookup Template
  4. Created by Tim Ford aka SQLAgentMan
  5. and
  6. As 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!
  7. ******************************************************************************************************
  9. Replace template parameter by using on of the following methods:
  10. 1. Control + Shift + M
  11. 2. Query\Specify Values for Template Parameters from SSMS Menu
  12. Hit F5 and enjoy!
  13. */
  14. SELECT OBJECT_SCHEMA_NAME(TB.object_id) AS schema__name
  15. , AS object__name
  16. , AS column__name
  17. , TB.is_ms_shipped AS is_system_object
  18. , type_desc AS object_type
  19. , AS data__type
  20. , TY.is_nullable
  21. , TY.max_length
  22. , TY.precision
  23. , TY.scale
  24. , TY.collation_name
  25. FROM sys.all_columns AC
  26. LEFT JOIN sys.tables TB ON AC.object_id = TB.object_id
  27. INNER JOIN sys.types TY ON AC.system_type_id = TY.system_type_id
  28. WHERE = ''
  29. ORDER BY 1,2


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:

  1. WHERE 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.