CODEWITHSUNDEEP
mysqlsql-like
Mansoor
Mansoor

Reputation: 285

Show full tables using both LIKE & WHERE clause

I need to select range of table names without views using only one query:

Example:

SHOW FULL TABLES LIKE '%table%' WHERE "Table_type"<>'VIEW'

MySQL does not allow to use these both operators in one query and it's using stupid column name like Tables_in_(mydbname) which contains DB name instead of using just simple column name like "Table_name". The problem is that I don't know the DB name, so I can't use the following query:

SHOW FULL TABLES WHERE "Table_type"<>'VIEW' AND "Tables_in_????" LIKE '%table%'

It would be great to use numbers instead of names like:

SHOW FULL TABLES WHERE COLUMN(1) LIKE '%table%' AND COLUMN(2) <>'VIEW'

but there is no such function.

I can't use INFORMATION_SCHEMA because the access to this table can be restricted by hosting provider (or I am wrong?) and my application is for wide distribution.

What would you advise me?

Upvotes: 2

Views: 7454

Answers (1)

Lukas Eder
Lukas Eder

Reputation: 220952

Query the INFORMATION_SCHEMA, in particular the TABLES and COLUMNS tables are interesting for you.

An example:

SELECT *
  FROM INFORMATION_SCHEMA.TABLES t
  JOIN INFORMATION_SCHEMA.COLUMNS c
    ON t.TABLE_CATALOG = c.TABLE_CATALOG
   AND t.TABLE_SCHEMA = c.TABLE_SCHEMA
   AND t.TABLE_NAME = c.TABLE_NAME
 WHERE [some field] LIKE '%some_value%'
   AND ...

Upvotes: 4

Related Questions