SELECT COLUMN_NAME, COLUMN_KEY FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = ‘[schema_db_name]’ AND TABLE_NAME = ‘[name_of_table]’ and COLUMN_KEY IN(‘PRI’, ‘UNI’);

Con esta consulta obtenemos el nombre/s de la columna/s de la tabla [name_of_table] del esquema de base de datos [schema_db_name] que cumplen que sean PRIMARY o UNIQUE.

Por ejemplo si tenemos la tabla:

CREATE TABLE `companies` (
`company_id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(100) NOT NULL DEFAULT '',
`cif` VARCHAR(20) NULL DEFAULT '',
`created_by` INT(11) NOT NULL,
`creation_date` DATETIME NOT NULL,
`modified_by` INT(11) NULL DEFAULT NULL,
`modification_date` DATETIME NULL DEFAULT NULL,
`deleted_by` INT(11) NULL DEFAULT NULL,
`deleted_date` DATETIME NULL DEFAULT NULL,
PRIMARY KEY (`company_id`),
INDEX `companies_fk01` (`created_by`),
INDEX `companies_fk02` (`modified_by`),
INDEX `companies_fk03` (`deleted_by`),
CONSTRAINT `companies_fk01` FOREIGN KEY (`created_by`) REFERENCES `users` (`user_id`),
CONSTRAINT `companies_fk02` FOREIGN KEY (`modified_by`) REFERENCES `users` (`user_id`),
CONSTRAINT `companies_fk03` FOREIGN KEY (`deleted_by`) REFERENCES `users` (`user_id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;

Al realizar la búsqueda mostrará

COLUMN_NAME, COLUMN_KEY
company_id, PRIMARY KEY

Comparte, comenta y colabora.