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.