Finding tables in a database that have keyword in table name

If you are a data analyst/scientist at a large company, your job will inevitably involve a lot of data ETL at multiple databases, each contains maybe tens of schemas and hundreds/thousands of tables. Trying to find a table within the database can be difficult. However, this snippet can be a life saver to help you navigate through the database.

Why this can be useful

Because nobody knows every single table by its name. This may be due to several factors that are usually not well addressed in online tutorials.

Tutorials/Classes	Real life
Small database, simple schema, a few tables	Multiple large databases, hundreds/thousands of tables
All tables follow a naming convention that is consistent and self-explanatory	Depends
Instructor knows what he/she has in the database	“Legacy stuff? Nobody knows what’s in there”

How to do it

In SQL server

SELECT *, LOWER(table_catalog + '.' + table_schema + '.' + table_name) 
FROM INFORMATION_SCHEMA.TABLES 
WHERE table_name LIKE '%keyword%' 
ORDER BY table_name

In Postgres SQL

SELECT CONCAT(table_schema, '','', table_name) AS table_name
FROM information_schema.tables
WHERE table_name LIKE '%keyword%'
ORDER BY table_schema,table_name;'

In MySQL (untested)

SELECT table_name
FROM INFORMATION_SCHEMA.tables 
WHERE table_name LIKE '%keyword%'

In Oracle (untested)

SELECT table_name 
FROM all_tab_columns 
WHERE table_name = '%keyword%';

If you have DBA privileges, you can try this command instead:

SELECT table_name 
FROM dba_tab_columns 
WHERE table_name = '%keyword%';