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;

Comments

[Vincent Milum Jr]

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

[Allan Simonsen]

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