How to Find Tables that Contain a Specific Column in SQL Server

Data Tutorial

While extremely powerful as a relational database, SQL Server can be somewhat daunting at times when it comes to looking up underlying information about the database system itself.

To relieve these headaches in some small part, we’ll briefly explore how to find all tables in the database which contain a particular column name.

SQL Server Catalog Views

One basic concept to understand about SQL Server is that of catalog views, which are effectively database tables (catalogs in this case) that display system-wide information about the SQL Server Database Engine.

Querying System Information

All catalog views are accessed via a SELECT SQL statement FROM a specific catalog within the sys. namespace.

For example, the following statement can be used to view information about all database tables in the system via the sys.tables catalog:

SELECT
  *
FROM
  sys.tables

LIKE Statement and Wildcard Character

Before we get into how to extract all tables with a particular name, we should briefly explore what the LIKE statement does, as well as the wildcard (%) symbol, and how they are used together.

LIKE is used in a query to determine if a particular pattern of characters (typically the values of a specified column) match a formatted string of characters.

LIKE is often also used in conjunction with the % character, which represents a wildcard when attempting to match the pattern. When a % wildcard character is present in the pattern string, it indicates that any characters can be present in that location of the pattern string and still be considered a match.

For example, if we want to find all books where the title begins with “The” but can contain any characters thereafter, we’d use a statement like so:

SELECT
  title,
  primary_author,
  published_date
FROM
  books
WHERE
  title LIKE 'The%'

Observant readers might realize that the above pattern would not only match titles that had “The” at their beginning, but also any titles with words simply starting with the three letters “The” as well. Since % wildcards match any characters, if we only want to check for titles with the word “The”, adding a space is more appropriate:

SELECT
  title,
  primary_author,
  published_date
FROM
  books
WHERE
  title LIKE 'The %'

Selecting Tables Containing a Column Name

With our basic knowledge of both catalog views and the LIKE statement, we are now equipped to lookup all the tables in our system that contain a particular column name:

SELECT
  sys.columns.name AS ColumnName,
  sys.tables.name AS TableName
FROM
  sys.columns
JOIN ON
  sys.columns.object_id = sys.tables.object_id
WHERE
  sys.columns.name = 'ColumnName'

We need to combine information from two catalogs, sys.tables and sys.columns, so we’re using a JOIN statement. The two are associated by the object_id field, so we JOIN on that field.

From there, it’s a simple matter of selecting the ColumnName and TableName of our results, and finally, of course, only looking up records where sys.columns.name is equal to our ColumnName string.

However, this query will only find exact matches of the column name. If we want to find partial matches, we can use LIKE and % wildcard characters instead:

SELECT
  sys.columns.name AS ColumnName,
  sys.tables.name AS TableName
FROM
  sys.columns
JOIN ON
  sys.columns.object_id = sys.tables.object_id
WHERE
  sys.columns.name LIKE '%ColumnName%'

There we have it! A simple query to look up all tables and associated columns with a particular (or similar) column name in them.