How to delete a column from multiple tables in Mysql?

Asked

Viewed 32 times

0

I have a database of over 100 tables, and I would like to find some way to delete a specific column that all tables have in common. Is there any way? If so, how?

I tried to run the following excerpt, but it didn’t work:

ALTER TABLE 
(SELECT table_name
FROM information_schema.key_column_usage
WHERE CONSTRAINT_SCHEMA = 'nome_database' AND REFERENCED_TABLE_NAME = "usuario" ORDER BY table_name) DROP COLUMN id_usuario;
mysql

  • Isn’t it easier to write a small program, or script, with 100 commands? Of course, the question itself is valid, as a curiosity.

  • @epx I thought the same thing because it seems to be a task that would be done only 1x. Then never again.

1 answer

0


In the mysql, the table "information_schema.Columns" has the record of all columns in all tables, if you make a select you will find the table names:

SELECT TABLE_NAME 
  FROM information_schema.columns 
 WHERE column_name = 'id_usuario';

If you want to delete table columns from there you need to do two things:

I don’t have now how to assemble a functional example now, but can create a code process in this structure below to make these steps:

DECLARE nome_tabela VARCHAR(500);

DECLARE cursor_tabelas CURSOR FOR SELECT TABLE_NAME FROM information_schema.columns WHERE column_name = 'id_usuario';

OPEN cursor_tabelas;

LOOP
    FETCH cursor_tabelas INTO nome_tabela;
    
    SET @drop_command = CONCAT('ALTER TABLE ',nome_tabela,' DROP COLUMN id_usuario');
    PREPARE stmt FROM @drop_command;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    
    SELECT nome_tabela;
END LOOP;

CLOSE cursor_tabelas;

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.