50

There are too many tables in a db. how can I only show tables with certain patterns? Or is there a way I can do paging like "| more" in shell command?

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
user398384
  • 1,124
  • 3
  • 14
  • 21
  • not for mysql, but for oracle, you can do "select * from cat where table_name like '%xxxx'". the same functionality must exist for mysql – KevinDTimm Aug 04 '10 at 16:33

5 Answers5

90
show tables like 'pattern';
a'r
  • 35,921
  • 7
  • 66
  • 67
  • 12
    I also found `show tables like '%pattern%';` works too : ) – michaelmcgurk Jun 06 '16 at 07:29
  • 6
    @michaelmcgurk, the % is the wildcard in any like statement. Therefore a'r was correct...the pattern can be anything you are searching for, including wildcards. – Kevin Finkenbinder Jan 25 '17 at 16:28
  • 1
    What is the way if I want to SHOW TABLES LIKE 'xxx_%' but I don't want the underscore character as a wildcard... ? SHOW TABLES LIKE 'xx#_%' ESCAPE '#' don't work for me... – Paolo Biavati Nov 23 '20 at 12:13
8
  • use show tables like 'pattern'
  • pattern is a string using wildcard characters "%","_"
  • % matches any number of characters, even zero characters.
  • _ matches exactly one character.

for example:

  • show tables like 'test%' will filter tables such as "test1,testF,test111,testFoo"

  • show tables like 'test_' will filter tables such as "test1,testF"

wengeezhang
  • 2,911
  • 1
  • 17
  • 10
3

You don't have to use show tables, you can also query information_schema.TABLES using any filter.

a1ex07
  • 36,826
  • 12
  • 90
  • 103
  • Can you give an example or link? Where is the information_schema.TABLES located? – Despertar Mar 04 '12 at 00:01
  • Information_schema is a MySQL system DB. You can check http://dev.mysql.com/doc/refman/5.0/en/information-schema.html for details – a1ex07 Mar 04 '12 at 13:51
  • SELECT * from information_schema.TABLES WHERE TABLE_NAME LIKE 'oc_%'; – t2d Nov 22 '19 at 16:29
0

In Spark SQL you need to use an asterisk symbol, SHOW tables LIKE '*table_name*

Sam Comber
  • 1,237
  • 1
  • 15
  • 35
-4

this can be used to have a glimpse of all the tables

select * from tab;

and identify the columns names. Thereafter, use 

select * from tab where tname like '%D_';
Wim Ombelets
  • 5,097
  • 3
  • 39
  • 55
user3620269
  • 1
  • 1