4

I have created one temporary table named "table1". I am trying to list the columns of my temp table. I am not getting any values. Here is my mysql query.

SELECT column_name
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE table_name = 'table2';

Any body help me please? Thanks

user1557020
  • 301
  • 3
  • 6
  • 20
  • See http://stackoverflow.com/a/16296395/575376 – juergen d Aug 05 '14 at 12:30
  • Please try to google it, INFORMATION_SCHEMA doesn't lists temporary tables.. – AK47 Aug 05 '14 at 12:54
  • 1
    I upvoted this because the [INFORMATION_SCHEMA.COLUMNS](https://dev.mysql.com/doc/refman/5.5/en/columns-table.html) documentation does not mention anything about temporary tables. The [INFORMATION_SCHEMA.TABLES](https://dev.mysql.com/doc/refman/5.5/en/tables-table.html) documentation does say, "Currently, the TABLES table does not list TEMPORARY tables." But again, that says nothing about the COLUMNS table. – humbads Sep 07 '15 at 12:42

3 Answers3

10

You cannot get the temporary table columns using the INFORMATION_SCHEMA

The only way which you can use is to go with SHOW CREATE TABLE table2

"INFORMATION_SCHEMA.COLUMNS" does not contains columns of temporary table.

Rahul Tripathi
  • 168,305
  • 31
  • 280
  • 331
6

You can use SHOW COLUMS to achieve this.

Example Table:

CREATE TEMPORARY TABLE SalesSummary (
product_name VARCHAR(50) NOT NULL, 
total_sales DECIMAL(12,2) NOT NULL DEFAULT 0.00,
avg_unit_price DECIMAL(7,2) NOT NULL DEFAULT 0.00,
total_units_sold INT UNSIGNED NOT NULL DEFAULT 0
 );

Command: SHOW COLUMNS FROM SalesSummary;

Outout:

mysql> SHOW COLUMNS FROM SalesSummary;
+------------------+------------------+------+-----+---------+-------+
| Field            | Type             | Null | Key | Default | Extra |
+------------------+------------------+------+-----+---------+-------+
| product_name     | varchar(50)      | NO   |     | NULL    |       |
| total_sales      | decimal(12,2)    | NO   |     | 0.00    |       |
| avg_unit_price   | decimal(7,2)     | NO   |     | 0.00    |       |
| total_units_sold | int(10) unsigned | NO   |     | 0       |       |
+------------------+------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

More details are in the manual Section 13.7.5.5 for MySQL 5.7. Link

Another possibility is using SHOW CREATE TABLE:

 mysql> SHOW CREATE TABLE SalesSummary\G
 *************************** 1. row ***************************
        Table: SalesSummary
 Create Table: CREATE TEMPORARY TABLE `SalesSummary` (
  `product_name` varchar(50) NOT NULL,
  `total_sales` decimal(12,2) NOT NULL DEFAULT '0.00',
  `avg_unit_price` decimal(7,2) NOT NULL DEFAULT '0.00',
  `total_units_sold` int(10) unsigned NOT NULL DEFAULT '0'
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
 1 row in set (0.00 sec)

Here are some more details.

In Mysql 5.7 there is a seperate table called INNODB_TEMP_TABLE_INFO to achieve this.

Stefan
  • 679
  • 2
  • 9
  • 21
  • thanks a lot, it was useful. unfortunately i can able to check answered only one. – user1557020 Aug 05 '14 at 16:09
  • if you create a table in session then you can access, but suppose your application create randomly temporary table as per user login, then You cant see the list of the table for all sessions. – Abhijit Jagtap Dec 12 '17 at 13:03
  • Temporary tables in MySQL are always per session only. [A TEMPORARY table is visible only within the current session, and is dropped automatically when the session is closed. This means that two different sessions can use the same temporary table name without conflicting with each other or with an existing non-TEMPORARY table of the same name.] https://dev.mysql.com/doc/refman/5.7/en/create-temporary-table.html – Stefan Dec 13 '17 at 16:00
5

It is possible to get a list of column names for any table.

This method will also work for TEMPORARY tables:

SET @columns_string = ''
;

SHOW
    COLUMNS
FROM
    `mysql`.`user`
WHERE
    @columns_string := CONCAT(`Field`, ',', @columns_string)
;

SELECT @columns_string
;

@columns_string will have a value of:

account_locked,password_lifetime,password_last_changed,password_expired,authentication_string,plugin,max_user_connections,max_connections,max_updates,max_questions,x509_subject,x509_issuer,ssl_cipher,ssl_type,Create_tablespace_priv,Trigger_priv,Event_priv,Create_user_priv,Alter_routine_priv,Create_routine_priv,Show_view_priv,Create_view_priv,Repl_client_priv,Repl_slave_priv,Execute_priv,Lock_tables_priv,Create_tmp_table_priv,Super_priv,Show_db_priv,Alter_priv,Index_priv,References_priv,Grant_priv,File_priv,Process_priv,Shutdown_priv,Reload_priv,Drop_priv,Create_priv,Delete_priv,Update_priv,Insert_priv,Select_priv,User,Host,

The problem with this solution (besides needing to parse a string) is that the SHOW COLUMNS command will always generate an (empty) resultset.

This means that you can't really use this method in a stored procedure that returns a SELECT (you will end up with more than one resultset).

sam-6174
  • 3,104
  • 1
  • 33
  • 34
  • 1
    This is brilliant! Can be used as stored procedure to get columns list of any table by itself and in another stored procedure. ``` CREATE PROCEDURE `get_column_list_hack`(schema_name VARCHAR(64), table_name VARCHAR(64), OUT `column_list` TEXT) BEGIN SET @cs = ''; PREPARE show_cols FROM CONCAT('SHOW COLUMNS FROM ', schema_name, '.', table_name, ' WHERE @cs := TRIM("," FROM CONCAT(@cs, ",", `Field`))'); EXECUTE show_cols; DEALLOCATE PREPARE show_cols; SET column_list = @cs; END ``` – petr Sep 20 '19 at 08:09
  • 1
    dude, that's pure genius :D – Radacina Oct 23 '20 at 18:21
  • It is working, Thanks. Additionally, it gives a result with no rows, just column names as Field, Type, Null, Key, Default, Extra. How can we ignore or not returning this result? Any idea? – rafidheen Feb 27 '23 at 11:56