How to find a SQL Server column name: Run this one simple query!

If you have a particularly large database, finding a specific column might be a bit of a challenge. Maybe you don’t recall exactly what the column name is, and you’re not sure what table it belongs to.

Luckily, Microsoft SQL Server has some build in system tables that can help us find a column name in our database.

Query the sys.all_columns system table

The sys.all_columns system table stores information about all the columns in our database. We can write a query that uses a JOIN to the sys.tables system table to give us details about the table that the columns belong to also. For example:

SELECT tabl.name as 'Table Name', col.name as 'Column Name'
FROM sys.all_columns col
INNER JOIN sys.tables tabl
ON col.object_id = tabl.object_id
WHERE tabl.type = 'U'
ORDER BY tabl.name

Here is an example of the output of this query:

An example of finding a column name:

Let’s say I’m trying to locate a column that contains the word ‘Region‘ in it’s name. I can add a filter to the query above using wildcards to look for all columns that contain the word ‘Region‘:

SELECT tabl.name as 'Table Name', col.name as 'Column Name'
FROM sys.all_columns col
INNER JOIN sys.tables tabl
ON col.object_id = tabl.object_id
WHERE tabl.type = 'U'
AND col.name like '%Region%'
ORDER BY tabl.name

Here’s the output:

Looks like my database has two columns with the word ‘Region‘ somewhere in the name. They are in the EmployeeData and SalesRegions tables.

Another useful thing to see is the data type of those columns. We can do another simple JOIN to the sys.types system table to see the data type of the columns that are returned:

SELECT tabl.name as 'Table Name', col.name as 'Column Name', typ.name as 'Data type'
FROM sys.all_columns col
INNER JOIN sys.tables tabl
ON col.object_id = tabl.object_id
INNER JOIN sys.types as typ
ON col.user_type_id = typ.user_type_id
WHERE tabl.type = 'U'
AND col.name like '%Region%'
ORDER BY tabl.name

Here’s an example:

One thing I want to point out about these queries is that they filter on a table type of ‘U‘. This means they will only show us table/column information for user defined tables. It will not show us details for all the system tables/columns that come with SQL Server.

You should take a look at all the other columns available to us in the sys.all_columns system table. They can give you some great information about the columns in your database. Of course, you could also locate those columns in the Object Explorer of your SQL Server Management Studio and view the properties of the columns that way.

Next Steps:

Leave a comment if you found this tutorial helpful!

Do you know how to change a column name in SQL Server? Click the link to find out!

You might also enjoy the following FREE GUIDE:

FREE 1-page Simple SQL Cheat Sheet on the Top 10 Data Types you need to know!

This guide discusses the 10 most common data types you will encounter during your career as a database professional, condensed into a simple 1-page guide. This guide will definitely be a great resource for you to reference during your career. Download it today!

Thank you very much for reading!

Make sure you subscribe to my newsletter to receive special offers and notifications anytime a new tutorial is released!

If you have any questions, please leave a comment. Or better yet, send me an email!