There is a table with a large (or perhaps more redundant) number of columns. As one sql-request to delete columns in which there is no one value. The data array and the number of tables in the database do not allow to carry out this work manually. Thank.

  • Well, write all php. The first query, find the empty columns, then another one delete those where there is no data. - KoVadim pm

2 answers 2

Deleting a column can be done with the following construction:

ALTER TABLE table_name DROP field_name

Where

table_name is the name of the table in which the column will be deleted;
field_name is the name of the column to be deleted.

 $sql="ALTER TABLE search DROP id_num"; mysql_query($sql);

If we want to delete several fields at once, then we need to repeat DROP field_name for each column separated by commas:

 $sql="ALTER TABLE search DROP id_1, DROP id_2, DROP id_3"; mysql_query($sql);

    You can try something like this, generate scripts in this way and execute them. Not quite sure about the IF syntax, but I think you can find how to correctly. The general idea is to be based on the information_schema table. COLUMNS generate the necessary scripts using SQL.

     SELECT CONCAT('IF ( select count(*) from ',t.`TABLE_SCHEMA`,'`.`',t.`TABLE_NAME`,'` where `',t.`TABLE_SCHEMA`,'`.`',t.`TABLE_NAME`,'`.`',t.COLUMN_NAME,'` is null) = ( select count(*) from ',t.`TABLE_SCHEMA`,'`.`',t.`TABLE_NAME`,'`) THEN \n ALTER TABLE `', t.`TABLE_SCHEMA`, '`.`', t.`TABLE_NAME`, '` drop column `',t.`TABLE_SCHEMA`,'`.`',t.`TABLE_NAME`,'`.`',t.COLUMN_NAME,'`;\n END IF;') as sqlcode FROM `information_schema`.`COLUMNS` t WHERE 1 AND t.`TABLE_SCHEMA` = 'you_db_name' ORDER BY 1