DEV Community

Cover image for MariaDb Quick-tip #6 - Find table or column
Allan Simonsen
Allan Simonsen

Posted on

2 1

MariaDb Quick-tip #6 - Find table or column

MariaDB tips and tricks

This is part of a series of quick tips and tricks I have accumulated over the year, that I think can be useful for others.
If you have similar short tips and tricks please leave a comment.

Find table by name

If you have ever worked on a big database with a huge number of tables and you are not yet familiar with the whole data model, you know how hard it is to locate that specific table you are looking for. Or maybe you just need to get a list of tables that have similar names to put into that Power Point presentation you are working on.
Then the following query may be useful to you.

SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_ROWS  
  FROM information_schema.tables 
 WHERE table_name LIKE '%dep%'
Enter fullscreen mode Exit fullscreen mode

DBeaver screenshot

Find table that have a specific column

If you have been a programmer for some time and worked on different projects for different customers then you have probably come across databases with few or entirely without foreign key constraints. There can be a lot of reasons for not having foreign keys constraint and I won't go into them here, but one consequence of not having them is that finding tables that is related to other tables becomes hard so you need another way of finding related tables. And I do hope that the person who implemented the tables named the columns relating the tables by the same column name or a similar name. And if this is the case you can query all your database tables to find all tables containing columns with a specific name. This query below does that.

 SELECT CONCAT(t.TABLE_SCHEMA, '.', t.TABLE_NAME), c.COLUMN_NAME 
   FROM information_schema.TABLES t
   JOIN information_schema.COLUMNS c ON t.TABLE_NAME = c.TABLE_NAME 
  WHERE c.COLUMN_NAME LIKE '%name%'
    AND t.TABLE_SCHEMA <> 'information_schema'
Enter fullscreen mode Exit fullscreen mode

DBeaver screenshot

Do your career a big favor. Join DEV. (The website you're on right now)

It takes one minute, it's free, and is worth it for your career.

Get started

Community matters

Top comments (0)

AWS Security LIVE!

Tune in for AWS Security LIVE!

Join AWS Security LIVE! for expert insights and actionable tips to protect your organization and keep security teams prepared.

Learn More

đź‘‹ Kindness is contagious

Immerse yourself in a wealth of knowledge with this piece, supported by the inclusive DEV Community—every developer, no matter where they are in their journey, is invited to contribute to our collective wisdom.

A simple “thank you” goes a long way—express your gratitude below in the comments!

Gathering insights enriches our journey on DEV and fortifies our community ties. Did you find this article valuable? Taking a moment to thank the author can have a significant impact.

Okay