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;
Top comments (2)
Curious. Why not just do "DESC
table
" or "SHOW CREATE TABLEtable
"?I didn't know those, thanks for the tip!