0
SHOW COLUMNS FROM country ;

Shows information about all the columns in country and its dataTypes.

But following command gives error 

SELECT* COLUMNS FROM country where TYPE = INT;

Error:- /* SQL Error (1064): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'COLUMNS FROM country where TYPE= INT' at line 1 */

What is the right way to do it ?

Siraj Alam
  • 9,217
  • 9
  • 53
  • 65
Abhishek parikshya
  • 51
  • 1
  • 5
  • Inventing new syntax and hoping it works is rarely useful. In any case, this can't be done with non-dynamic SQL. This is because the SQL Data Query Language (eg. `SELECT`) *requires* a fixed query shape. Now, if you could take the output of `SHOW COLUMNS ..` (or a query against another metadata table) and use it as a resultset.. – user2864740 Jun 26 '16 at 18:22
  • It will probably be more useful to start with http://stackoverflow.com/questions/5648420/get-all-columns-from-all-mysql-tables (a `SELECT` against an information schema metadata table). However, the result still can't be used to replace replace a '*'-with-restrictions in non-dynamic SQL. – user2864740 Jun 26 '16 at 18:26

3 Answers3

0

The first command 

SHOW COLUMNS FROM country ;

work mysql data dictionary .. so you can see the structure of the table (name and type )

the second work on real table 

select * COLUMNS FROM country where TYPE= INT;

and probably you don't have created in your table a column with name TYPE ..

You should use for obtain the column with the INT type

select * from INFORMATION_SCHEMA.COLUMNS 
where table_name = 'country' 
and DATA_TYPE = 'INT'
ScaisEdge
  • 131,976
  • 10
  • 91
  • 107
  • I am getting this error select * from INFORMATION_SCHEMA.COLUMNS where table_name = 'country' and type = 'INT'; /* SQL Error (1054): Unknown column 'type' in 'where clause' */ – Abhishek parikshya Jun 26 '16 at 18:43
  • Again the error is coming /* SQL Error (1064): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select * from INFORMATION_SCHEMA.COLUMNS where table_name = 'country' and DA' at line 5 */ – Abhishek parikshya Jun 26 '16 at 18:51
0

use this to get the COLUMN Names. You must change the SCHEMA_NAME, TABLE_NAME. The result is ordert by the position in table

SELECT `COLUMN_NAME`
FROM `information_schema`.`COLUMNS`
WHERE
  `TABLE_SCHEMA` = 'YourSchema'
AND 
  `TABLE_NAME` = 'table1'
AND
  `DATA_TYPE`  IN ('INT')
ORDER BY
  `ORDINAL_POSITION`;
Bernd Buffen
  • 14,525
  • 2
  • 24
  • 39
0

You have an error in your syntax. Try this

SHOW COLUMNS FROM country WHERE Type = 'int';

Carefully put single quote in data type

It will show you the columns with datatype int. Now use these columns in select statment.

SELECT [columns with int dataTye] FROM country

Siraj Alam
  • 9,217
  • 9
  • 53
  • 65