Query to find column names in a SQL Server database

Whenever I’m considering making database schema changes, I often find the need to search the database I’m working with for columns with a particular name.

It can be especially valuable to do this when dealing with a large model consisting of many tables and columns. In this situation, I’m typically seeking to establish whether a similar column already exists or perhaps attempting to build up a general picture of how the tables within the database are connected to each other.

Invalid object name 'Foo'.

If you’ve ever seen the above error message following a software deployment, then you’ll also appreciate having the ability to quickly search for columns by their name. In this scenario, you can look for the column within a good version of the database to see what you’re missing in production.

In this article, I demonstrate a quick and easy way of searching for columns using a simple SQL query.

The Query

You can run the query below against a SQL Server database to find existing columns which may be related to the new column (or columns) you are creating or to the feature you are developing.

DECLARE @ColumnName AS VARCHAR(MAX)
SET @ColumnName = '%COLUMN_TO_FIND%'
 
SELECT sys.schemas.name        AS [Schema],
       sys.tables.name	       AS [Table],
       sys.columns.name        AS [Column],
       sys.types.name          AS [Data Type],
       sys.columns.max_length  AS [Length],
       sys.columns.is_nullable AS [Is Nullable]
	        
FROM sys.schemas
 
INNER JOIN sys.tables  ON sys.schemas.schema_id    = sys.tables.schema_id
INNER JOIN sys.columns ON sys.tables.object_id     = sys.columns.object_id
INNER JOIN sys.types   ON sys.columns.user_type_id = sys.types.user_type_id
 
WHERE sys.columns.name LIKE @ColumnName
 
ORDER BY sys.tables.name, sys.columns.name

Before executing the SQL, first of all, make sure that you update the value of the @ColumnName variable (the '%COLUMN_TO_FIND%' text) with the name, or partial name, of the column you are looking for.

The Results

After running the query you will see a result set with the matching column names, along with both the schema and the name of the associated table that each matching column belongs to.

If you run the query against the AdventureWorks database when searching for the text '%product%', the output will look similar to the screenshot below.

The results are helpfully ordered by the table name, then by the column name so that you can easily find what you are looking for.

Some bonus columns such as the column data type and length are also included. I find these metrics are useful when searching for a foreign key column or a notes column, for example.

Alternatives

There are other ways of getting similar information to the above from your database e.g. you can select from the INFORMATION_SCHEMA.COLUMNS system table and your query will then work across any database which adheres to the ANSI Standard.

I find it useful, however, when working with SQL Server databases to include some of the extra, non-standard, information which the above query is able to provide.

Usage

Feel free to use and amend this script as you see fit. There is scope to select additional information such as table creation/modification dates and a number of other lower-level attributes.

Comments

This site uses Akismet to reduce spam. Learn how your comment data is processed.