Mountain trail through Rocky Mountain National Park

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 Template
Created by Tim Ford aka SQLAgentMan and

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!

	Replace template parameter by using on of the following methods:
		1. Control + Shift + M 
		2. Query\Specify Values for Template Parameters from SSMS Menu
	Hit F5 and enjoy!

SELECT OBJECT_SCHEMA_NAME(TB.object_id) AS schema__name
	, AS object__name
	, AS column__name
	, TB.is_ms_shipped AS is_system_object
	, type_desc AS object_type 
	, AS data__type
	, TY.is_nullable
	, TY.max_length
	, TY.precision
	, TY.scale
	, TY.collation_name
FROM sys.all_columns AC 
	LEFT JOIN sys.tables TB ON AC.object_id = TB.object_id
	INNER JOIN sys.types TY ON AC.system_type_id = TY.system_type_id
WHERE = '<column_name,,foo>'


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 LIKE '%<column_name,,foo>%'

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.

Hide comments


  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.