List temporary table columns in mysql

mysql temporary table
mysql create temporary table in stored procedure
mysql drop temporary table if exists
mysql temporary table variable
mysql stored procedure return temporary table
mysql temporary table performance
mysql show temporary tables
temporary table mysql innodb

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

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.

14.16.7 InnoDB INFORMATION_SCHEMA Temporary Table Info Table, Otherwise, these fields report FALSE . Create a compressed temporary table. mysql> CREATE TEMPORARY TABLE t2 (c1� mysql> CREATE TEMPORARY TABLE DemoTable745 ( StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY, StudentName varchar(100), StudentAge int, StudentAddress varchar(100), StudentCountryName varchar(20) ); Query OK, 0 rows affected (0.00 sec) Following is the query to list temporary table columns in MySQL−

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.

How to list temporary table columns in MySQL?, To list temporary table columns in MySQL, let us first create a temporary table. Here's an example. We have created a temporary table with� To show all columns of a table, you use the following steps: Login to the MySQL database server. Switch to a specific database. Use the DESCRIBE statement.; The following example demonstrates how to display columns of the orders table in the classicmodels database.

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).

MySQL - Temporary Tables, When you issue a SHOW TABLES command, then your temporary table would not be listed out in the list. Now, if you will log out of the MySQL session and then � To list all columns in a table, we can use the SHOW command. Let us first create a table. mysql> create table ColumnsList -> ( -> id int, -> Firstname varchar(200), -> LastName varchar(100), -> Age int, -> Address varchar(300), -> CollegeName varchar(100) -> ); Query OK, 0 rows affected (1.33 sec)

MySQL Temporary Table, This tutorial discusses MySQL temporary table and shows you step by step how to create, use A temporary table is very handy when it is impossible or expensive to query data that Previous How To Use The MySQL Generated Columns. In MySQL, a temporary table is a special type of table that allows you to store a temporary result set, which you can reuse several times in a single session. A temporary table is very handy when it is impossible or expensive to query data that requires a single SELECT statement with the JOIN clauses.

8.4.4 Internal Temporary Table Use in MySQL, Presence of any string column with a maximum length larger than 512 (bytes for binary strings, characters for nonbinary strings) in the SELECT list, if UNION or� The number of columns (N_COLS) is 4 rather than 1 because InnoDB always creates three hidden table columns (DB_ROW_ID, DB_TRX_ID, and DB_ROLL_PTR). PER_TABLE_TABLESPACE and IS_COMPRESSED report TRUE for compressed temporary tables.

Temporary Tables in MySQL, mysql> CREATE TEMPORARY TABLE temp_table (id int primary key noteworthy that we can specify the engine for temporary tables, as shown in listing 01. TEXT or BLOB fields are present (the MEMORY engine doesn't� 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. (The existing table is hidden until the temporary table

Comments
  • See stackoverflow.com/a/16296395/575376
  • Please try to google it, INFORMATION_SCHEMA doesn't lists temporary tables..
  • I upvoted this because the INFORMATION_SCHEMA.COLUMNS documentation does not mention anything about temporary tables. The INFORMATION_SCHEMA.TABLES documentation does say, "Currently, the TABLES table does not list TEMPORARY tables." But again, that says nothing about the COLUMNS table.
  • thanks a lot, it was useful. unfortunately i can able to check answered only one.
  • 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.
  • 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.] dev.mysql.com/doc/refman/5.7/en/create-temporary-table.html
  • 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 ```