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;