2

When I am writing code to go with a certain MySQL query, I like to do a basic select statement to see the columns, and to perhaps see what data is available as a sample. Sometimes, the table I would like to look at has no data, so I get a completely empty query.

Now, I know I can use something like "show create table [table_name]" to see the query; similarly, I am aware that it's possible to show the tables with a join statement, but the syntax is just complex enough that I don't always remember it, and I don't want to add complexity to every simply query I want to make, just to handle the occasional empty table.

Thus, I was wondering: is there a setting in MySQL's client, that would allow me to see the column names every time, even when the table is empty?

  • Afaik no (but check dev.mysql.com/doc/refman/5.5/en/mysql-command-options.html and try to experiment with those --column-X) but there is show columns from <table> which should be easy enough to remember. Another way is to do explain extended select * from <table>; show warnings; - the warning will output the query in "normalized" form - the * will be expanded to a complete column list. – jkavalik Oct 8 '15 at 20:18
  • I forgot to include "show columns"; the closest that comes to what I want is "--column-type-info", but unfortunately, the meta-data provided is overkill... – Alpheus Oct 13 '15 at 15:21
  • Good idea for a setting in my opinion. Apparently GUI developers have thought this was a good idea as well, as it is the default behavior in many that I have used with several DB platforms when you issue a SELECT that returns no records. – WAF Jun 3 '16 at 12:26
2

This will always return a single row with NULL in all the columns of the table:

SELECT t.* 
FROM (SELECT 1) AS dummy 
  LEFT JOIN table_name AS t
    ON FALSE ;

This will always return a single row with NULL in all the columns of the table if the table is empty and real data from one row if it isn't empty:

SELECT t.* 
FROM (SELECT 1) AS dummy 
  LEFT JOIN table_name AS t
    ON TRUE
LIMIT 1 ;
0

If you simply want a basic overview of the table structure, describe some_table; will give you a dump of the column names and is easy to remember (and type).

  • I usually use "select * from some_table" because I want to see both the column names and some sample data. I'd like to be able to see the column names, even if the table is empty, so that I don't have to take the next step (usually something like "describe some_table"...) – Alpheus Oct 13 '15 at 15:23
  • As far as I'm aware, that's simply not doable. Mind you, trying to infer schema from the data is a massive violation of good DB practice; any data restrictions beyond the col type should really be in a CHECK restraint, but sadly I don't believe MySQL supports those. – Vector Gorgoth Oct 13 '15 at 20:16
  • I would agree that trying to infer schema from data isn't a good idea, but when I'm working on displaying the data, or processing it, and I want a quick reminder of both the column names and get a feel for what the data looks like (not just the data types, but the types of stuff that gets stored), nothing beats an old fashioned "select" statement. Unfortunately, while comments here have pointed me to possible solutions that I've been able to research, I have to conclude that you are right: what I want isn't doable, so I'm stuck with trailing "show columns"... – Alpheus Oct 14 '15 at 6:25
  • Sadly, with an empty table, you'll get more info from describe $table than you would from a select anyway, since the select wouldn't even tell you the column types. I do use a similar trick for self-reminder purposes--select * from $table limit 1;--but that only works when there's data. – Vector Gorgoth Oct 15 '15 at 15:22
0

Below query is similar to what describe is doing. Only difference is it will also give you reference tables and reference columns for foreign keys.

    SET @row=0;
    SELECT 
    @row:=@row + 1 AS ID,
        c.COLUMN_NAME AS 'Column Name',
        c.COLUMN_TYPE AS 'Column Type',
        c.IS_NULLABLE AS 'Is Null?',
        (CASE
            WHEN c.COLUMN_KEY = 'MUL' THEN 'FK'
            ELSE c.COLUMN_KEY
        END) AS 'Column Key',
        k.REFERENCED_TABLE_NAME AS 'Reference Table',
        k.REFERENCED_COLUMN_NAME AS 'Reference Column'
    FROM
        information_schema.tables t
            INNER JOIN
        information_schema.COLUMNS c ON c.table_schema = t.table_schema
            AND c.table_name = t.table_name
            LEFT OUTER JOIN
        information_schema.key_column_usage k ON k.table_schema = t.table_schema
            AND k.table_name = t.table_name
            AND k.column_name = c.column_name
            INNER JOIN
        information_schema.COLUMNS i ON i.table_schema = t.table_schema
            AND i.table_name = t.table_name
    WHERE
        t.table_schema = 'database_name'
            AND i.column_name = 'VERSION_NO'
            AND t.TABLE_NAME = 'Table_name'
    GROUP BY t.table_name , c.ORDINAL_POSITION
    ORDER BY ID;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.