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
catalog views are accessed via a
SELECT SQL statement
FROM a specific catalog within the
For example, the following statement can be used to view information about all database tables in the system via the
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.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
TableName of our results, and finally, of course, only looking up records where
sys.columns.name is equal to our
However, this query will only find exact matches of the column name. If we want to find partial matches, we can use
% 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.