Question 20157 in Mysql Question posted in
The official MySQL documentation can be found here.

Stored procedure to return table names that match pattern – Mysql

I am trying to write a stored procedure in MariaDB/MySQL that will return table names, if any, within the schema that matches a pattern provided by the parameters. Guessing this is accomplished by querying the information_schema table, but any advice and example would be appreciated.

Tags:

2

Answers


  1. 0

    You can use simple select like this:

    SELECT TABLE_SCHEMA, TABLE_NAME
FROM information_schema.tables
WHERE TABLE_NAME like '%m%' and TABLE_SCHEMA = 'mysql';

    Here we select all tables from schema mysql that contain m in the name.

    Surprisingly, you can access this information even on demo sites. Here is demo at dbfiddle.

    Login or Signup to reply.
  2. 0

    This query does what you want, if by pattern-matching you mean SQL’s LIKE-style matching.

    SELECT TABLE_NAME 
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA=DATABASE()
  AND TABLE_TYPE='BASE TABLE'
  AND TABLE_NAME LIKE '%pattern%';

    Pretty straightforward. You could put this into a stored procedure. Or not. Depending on the complexity of software deployment you’re looking for.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search