mysql - Command to drop all columns in a database that have prefix test_ to run

Question

How I can run a command in phpMyAdmin which will drop all columns in a database that have the prefix test_.

Answer 1

To drop a column from a table, use the syntax:

alter table <tablename> drop column <columnname>

To find all the columns in a table in a database that start with test_, do the following:

select column_name
from INFORMATION_SCHEMA.columns
where table_name = <table_name> and
      table_schema = <schema_name> and
      left(column_name, 5) = 'test_'  -- not using "like" because '_' is a wildcard char

If you were doing this manually, I would recommend running the following query and then pasting the results in to a mysql query interface:

select concat('alter table ', table_name, ' drop column ', column_name)
from INFORMATION_SCHEMA.columns
where table_name = <table_name> and
      schema_name = <schema_name> and
      left(column_name, 5) = 'test_'

You can do something similar in code, by running the query, returning the results and then running each row as a query.