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:
