Have you ever found yourself searching through database tables trying to find a specific column?

Many databases have hundreds (or maybe even thousands), of tables within them, so searching through these in SSMS can be a slow process.

If you recently started at a new company and you are trying to learn the database structures, it could take some to memorize things. One of the challenges I was struggling with when I first started my BI analyst job was finding all of the instances where a column was used.

I came across an SQL query that has been immensely useful in helping to quickly find all of the tables where a column is used within a database.

You can use the below SQL query examples to search for column keywords, data types, or exact column names, as shown below.

SQL query to search for columns with a keyword:

SELECT table_name [Table Name], 
       column_name [Column Name]

FROM   information_schema.columns 

WHERE  COLUMN_NAME LIKE '%invoice%';
-- replace invoice with the column name keyword you are looking for

SQL query to search for columns with a specific data type:

SELECT table_name [Table Name], 
       column_name [Column Name]

FROM   information_schema.columns

WHERE  DATA_TYPE = 'geography';
-- replace geography with the column data type you are looking for

SQL query to search for columns with an exact column name:

SELECT table_name [Table Name], 
       column_name [Column Name]

FROM   information_schema.columns 

WHERE  COLUMN_NAME = 'DealerId'; 
-- replace DealerId with the column name you are looking for

Example Results Set Below: