Archive for August, 2011

Drop Column If Exists in MySQL

IF EXISTS ( SELECT * FROM information_schema.columns WHERE table_name = 'country' AND column_name = 'created_at' AND table_schema = DATABASE() ) THEN
    ALTER TABLE `country` DROP COLUMN `created_at`;
END IF;
Because DROP COLUMN IF EXISTS isn't supported by MySQL.

Comments

Show All Columns on a MySQL Table

SELECT * FROM information_schema.columns WHERE table_name = ‘my_super_table_name’ AND table_schema = ‘my_super_database_name’;

Comments

Show All Indexes on a MySQL Database

SELECT TABLE_NAME, INDEX_NAME, COLUMN_NAME, CARDINALITY, INDEX_TYPE FROM information_schema.statistics WHERE INDEX_NAME != ‘PRIMARY’ AND table_schema = ‘my_super_database’;

Comments