Have you ever needed to find all of the tables in a database that contain a field called _____ (insert field name here). It’s actually pretty easy to do. Microsoft was kind enough to provide us with a number of system catalog views that give us a plethora of useful information. One of these views is “sys.columns”. You can use sys.columns to figure out every table that has a column with a specific name.

For example. Let’s say you had a column called ‘ApplicationId’ on a specific table and you wanted to change the length of that column from varchar(10) to varchar(15). Chances are your application may use this field on several different tables. If this is the case then obviously you would want to make sure that you changed this field on every table that uses this field to prevent your application from errors if it attempts to insert a 15 character value into this field in any given table. You know of a few tables that have this ‘ApplicationId’ field, but you’re getting older and your memory just isn’t what it used to be. So to make sure that you get all of them you can use sys.columns like this:

-----------------------------------------------------------------------
select object_name([object_id]), * from sys.columns 
where name = 'ApplicationId'
order by object_name([object_id])
-----------------------------------------------------------------------

Notice that I used the system function “object_name()” to get the table name. Using the object_name() function gives me the table name without having to join to the sys.objects or sys.tables system catalog views to get the name. Of course if you needed more information about that table you could make this join on object_id like this:

-------------------------------------------------------------------------------------------
select a.name, b.name, b.[type], b.type_desc, b.[schema_id], schema_name(b.[schema_id])
from sys.columns as [a], sys.objects as [b]
where a.name = 'ApplicationId' and b.[object_id] = a.[object_id]
order by b.name
-------------------------------------------------------------------------------------------

This alows me to gather a little more information about the table that the field resides on such as: “is it a view instead of an actual table”.