CODEWITHSUNDEEP
mysql
kmoney12
kmoney12

Reputation: 4490

Single Column From SHOW Command

I am using this to get my PRIMARY Index Columns.

SHOW INDEX FROM `table` WHERE Key_name='PRIMARY'

It returns a table with type, column_name, Key_name, etc.

I only want the Column_name column.

I tried:

SHOW INDEX.Column_name FROM `table` WHERE Key_name='PRIMARY'

But it did not work. Any ideas?

Upvotes: 2

Views: 593

Answers (1)

RolandoMySQLDBA
RolandoMySQLDBA

Reputation: 44343

To get all columns from a PRIMARY KEY (in order) for mydb.mytable, run this:

SELECT column_name
FROM information_schema.statistics
WHERE table_schema='mydb'
AND table_name='mytable'
AND index_name='PRIMARY'
ORDER BY seq_in_index;

To get all columns from a PRIMARY KEY (in order) for mytable in the current database, run this:

SELECT column_name
FROM information_schema.statistics
WHERE table_schema=DATABASE()
AND table_name='mytable'
AND index_name='PRIMARY'
ORDER BY seq_in_index;

Here is a sample table:

mysql> show create table cacti.host_snmp_cache\G
*************************** 1. row ***************************
       Table: host_snmp_cache
Create Table: CREATE TABLE `host_snmp_cache` (
  `host_id` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `snmp_query_id` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `field_name` varchar(50) NOT NULL DEFAULT '',
  `field_value` varchar(255) DEFAULT NULL,
  `snmp_index` varchar(255) NOT NULL DEFAULT '',
  `oid` text NOT NULL,
  PRIMARY KEY (`host_id`,`snmp_query_id`,`field_name`,`snmp_index`),
  KEY `host_id` (`host_id`,`field_name`),
  KEY `snmp_index` (`snmp_index`),
  KEY `field_name` (`field_name`),
  KEY `field_value` (`field_value`),
  KEY `snmp_query_id` (`snmp_query_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql>

Notice the PRIMARY KEY has 4 columns

Here is the query's execution:

mysql> SELECT column_name
    -> FROM information_schema.statistics
    -> WHERE table_schema='cacti'
    -> AND table_name='host_snmp_cache'
    -> AND index_name='PRIMARY'
    -> ORDER BY seq_in_index;
+---------------+
| column_name   |
+---------------+
| host_id       |
| snmp_query_id |
| field_name    |
| snmp_index    |
+---------------+
4 rows in set (0.00 sec)

mysql>

Using GROUP_CONCAT, you can collect the column names into a CSV list

mysql> SELECT GROUP_CONCAT(column_name) PrimaryKeyColumns
    -> FROM information_schema.statistics
    -> WHERE table_schema='cacti'
    -> AND table_name='host_snmp_cache'
    -> AND index_name='PRIMARY'
    -> ORDER BY seq_in_index;
+---------------------------------------------+
| PrimaryKeyColumns                           |
+---------------------------------------------+
| host_id,snmp_query_id,field_name,snmp_index |
+---------------------------------------------+
1 row in set (0.01 sec)

mysql>

Give it a Try !!!

Upvotes: 2

Related Questions