I’m writing a quick blog on an SQL query that comes in handy when you have many tables and columns to search through, but aren’t quite sure where everything is in a database. All you need is a keyword and this SQL query, and it could save you hours of searching.
The problem
I was recently working with a business who had a sprawling data architecture that had slowly grown and morphed into a monster over their 25+ years of operation. We were there to create order from the chaos. We needed to find the core bits of important information that the business wanted and streamline this into a clearer and more organised database. But before we could start on such a task, we needed to know where that important information was.
The business had over 1000 tables across several siloes and while some tables might only have two or three columns, others would have up to 400 columns. Without an ERD, there is no way, to efficiently look into every column of every table.
This is where the following SQL query comes in handy.
The solution
If you are new to a business or ever find yourself in a situation similar to that which is described above, keep this SQL query in your back pocket to save yourself time and frustration.
How to search all tables in an SQL database
The SQL query to search columns in all tables of a database is:
SELECT [INFORMATION_SCHEMA].[COLUMNS].[COLUMN_NAME] as 'ColumnName', [INFORMATION_SCHEMA].[COLUMNS].[TABLE_NAME] as 'TableName' FROM [INFORMATION_SCHEMA].[COLUMNS] WHERE [INFORMATION_SCHEMA].[COLUMNS].[COLUMN_NAME] LIKE '%Keyword%' ORDER BY [TableName], [ColumnName]
About the query
This query utilises the Information Schema which allows you to view the metadata associated with a database.
The first part of the query is selecting the COLUMN_NAME and TABLE_NAME columns from the COLUMNS metadata table. The WHERE clause is used to ensure that only columns which have a name that is similar to your search keyword. Note the LIKE clause is used, meaning that your keyword doesn’t even have to be an exact match to the column name.
If I am looking for relations between tables, sometimes I like to swap the columns in the ORDER BY statement to see all tables that contain a certain column.
To use the query
To use this query, I do the following steps:
- Open SSMS
- Connect to the relevant database
- Open a new query
- Ensure that I have the relevant database selected from the drop down in the top left corner
- Paste the above SQL into the query editor and replace “Keyword” with the keyword I am searching for (make sure to leave the “%” on either side).
- Execute the query and then I begin to explore any relevant tables that appear in the result.
I hope this helps you with exploring any complex databases that are new to you. Good luck and happy analysing!
