Is there any way to list all tables that contain certain keywords in psql?
In other words, is there a similar command as in mysql:
show tables like "%test%" ?
Add a comment
|
2 Answers
Just found the answer..
\dt *test* will pull tables: 123_test_234, test_234, 123_test etc.
\dt *test will pull tables: 123_test etc.
\dt test* will pull tables: test_234 etc.
Not sure if there is a similar question. If there is, maybe I should delete this.
1 Comment
raghu
I had to double quote the text like
\dt *"test"* or \dt "test"* to fetch the matching tables.The answer offered by @peipei uses the meta-command \dt, but if you want to filter out the columns except the table name (as SHOW TABLES do in mysql) you can do the following:
SELECT tablename
FROM pg_catalog.pg_tables
WHERE
schemaname != 'pg_catalog'
AND
schemaname != 'information_schema'
AND
tablename like '%test%';
Probably excluding the pg_catalog and information_schema is what you want to do in order to get the most common schemas where the non-system tables are created.
View tip: if you use that query a lot you can create a VIEW that behaves like a table of tables:
CREATE VIEW tables AS SELECT tablename FROM pg_catalog.pg_tables WHERE schemaname != 'pg_catalog' AND schemaname != 'information_schema';
and then you can query it and remind it easily:
SELECT * FROM tables WHERE tablename LIKE '%test%';
Tip 2: Foreign tables won't appear in your search if you have them. You would need to query then in this other mode:
SELECT foreign_table_name
FROM information_schema.foreign_tables
WHERE foreign_table_name LIKE '%test%';
