DEV Community

Cover image for MariaDd Quick-tip #10 - Select table definition
Allan Simonsen
Allan Simonsen

Posted on

MariaDd Quick-tip #10 - Select table definition

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.

Select table definition

Have you ever had the task of documenting a whole database or maybe just a few tables some programmer created years ago. I have, and I quickly found that having a query to directly select the schema of a table was a great timesaver.
Or maybe you don't have access to the production database, but the bugs or performance issues you experience, indicate a missing index or a wrong column definition, so you need to send a request for the IT operations department to fetch the information about a table from the database for you.
In both cases the queries below can be very useful.

SET @DatabaseName := 'test_db';
SET @TableName := 'departments';

SELECT c.COLUMN_NAME, c.COLUMN_TYPE, c.IS_NULLABLE
  FROM INFORMATION_SCHEMA.`COLUMNS` c 
 WHERE c.TABLE_SCHEMA = @DatabaseName
   AND c.TABLE_NAME = @TableName;

SELECT s.TABLE_NAME,
       s.INDEX_NAME,
       group_concat(s.COLUMN_NAME ORDER BY s.SEQ_IN_INDEX ) AS index_columns,
       s.INDEX_TYPE,
       CASE s.NON_UNIQUE
            WHEN 1 THEN 'Not Unique'
            ELSE 'Unique'
       END AS is_unique
  FROM information_schema.STATISTICS s
 WHERE s.INDEX_SCHEMA = @DatabaseName
   AND s.TABLE_NAME = @TableName
 GROUP BY s.TABLE_NAME,
          s.INDEX_NAME,
          s.INDEX_TYPE,
          s.NON_UNIQUE
 ORDER BY s.TABLE_NAME,
          s.INDEX_NAME;

SELECT tc.CONSTRAINT_NAME, tc.CONSTRAINT_TYPE  
  FROM information_schema.TABLE_CONSTRAINTS tc 
 WHERE tc.CONSTRAINT_SCHEMA = @DatabaseName
   AND tc.TABLE_NAME = @TableName;
Enter fullscreen mode Exit fullscreen mode

DBeaver screenshot
DBeaver screenshot
DBeaver screenshot

Top comments (2)

Collapse
 
darkain profile image
Vincent Milum Jr

Curious. Why not just do "DESC table" or "SHOW CREATE TABLE table"?

Collapse
 
coderallan profile image
Allan Simonsen

I didn't know those, thanks for the tip!