Finding columns in tables

In the past days, I have found a lot of people asking themselves, 'how do I know in which tables I can find xyz column name, and how do I know I have all the tables?' The answer to this question is often used to make database wide updates and perform a number of maintenance functions for master records and transactions that were not recorded as originally intended. The following example query attempts to solve the issue by exposing all tables were the account index (ACTINDX) column is found within the Fabrikam database (TWO).

-- Created by Mariano Gomez, MVP
-- This code is licensed under the Creative Commons 
-- Attribution-NonCommercial-ShareAlike 3.0 Unported License.
-- http://creativecommons.org/licenses/by-nc-sa/3.0/legalcode
--
DECLARE @ColumnName VARCHAR(30);

SET @ColumnName = 'ACTINDX';

SELECT DISTINCT RTRIM(OBJS.NAME)
FROM SYS.COLUMNS COLS
  INNER JOIN SYS.OBJECTS OBJS ON (COLS.OBJECT_ID = OBJS.OBJECT_ID)
  INNER JOIN SYSINDEXES INDX ON (COLS.OBJECT_ID = INDX.ID)
WHERE (COLS.NAME = @ColumnName) AND (OBJS.TYPE = 'U') AND (INDX.ROWCNT <> 0);
GO

The results are as shown below:

CM00100
CM20400
GL00100
GL00103
GL00104
GL00105
GL00201
GL10001
GL10002
GL10110
GL10111
GL20000
GL30000
GL40101
IV30500
IVC10300
IVC10500
MC00200
MC00201
MC00300
PA00002
PM10500
PM30700
PM80700
POP10390
POP30390
RM10601
RM30601
SOP10102
SOP10105
SVC00230
SVC00231
SVC00609
SVC00731
SVC05030
SY01100
SY03100
TX00201
UPR00100
UPR10209
UPR19901
UPR30401
UPR40500
UPR41200

By checking the rowcount in the sysindexes table, we are ensuring that we focus our efforts in those tables that contain data. Very helpful, isn't it?

SQL Server 2005 and SQL Server 2008

If working with Microsoft SQL Server 2005 or above, the query can be simplified as follows:

SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = 'ACTINDX' ORDER BY TABLE_NAME

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/


UPDATES TO THIS ARTICLE:
12/13/2008: Modified SQL query to look at system views and change article formatting.
01/08/2009: Added SQL Server 2005 and 2008 query to INFORMATION_SCHEMA