Search code examples
mysqlinformation-schema

Desc primary key of all tables in MYSQL

I want to get the result of Desc for only specific columns of each table in the information schema, let's say Primary Key.

Can this be done?

I tried things like 

 SELECT * FROM (DESC TABLENAME) WHERE ....;

but it did not work.

Also I want this to work for all tables.

Solution

  • DESC aka DESCRIBE aka SHOW COLUMNS will not work in a subquery. But SELECT will. You can for example replace

    SHOW COLUMNS FROM t IN test LIKE '%2';

    with

    SELECT column_name AS `Field`, column_type AS `Type`,
is_nullable AS `Null`,
column_key as 'Key',
column_default AS `Default`, extra AS `Extra`
FROM information_schema.columns
WHERE table_schema = 'test'
AND table_name = 't'
AND column_name like '%2';

    and you'll get the same thing.