CODEWITHSUNDEEP
mysqlsql
Janno
Janno

Reputation: 542

Getting all table names which contains a specific keyword inside itself

I have a database which has 180 tables in it. Going through all of them one by one would be wasting my time.

The problem I am facing, is that I want to search a specific keyword from all tables and columns. So let's say that I got a database d with tables t1, t2, and so on, all tables have different column names and the string which I want to see must be LIKE '%connect%'.

To clarify, the %connect% must be inside the table contents (i.e. inside a row of a table).

If this is not possible by a single query, maybe you can point me into the right direction how to do this programmatically.

Upvotes: 1

Views: 1257

Answers (1)

Razzka
Razzka

Reputation: 641

Table's names:

select t.table_name from information_schema.tables t where t.table_name like '%connect%';

Column's names:

SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'my_database' AND TABLE_NAME = 'my_table';

Upvotes: 3

Related Questions