CODEWITHSUNDEEP
mysql
holland
holland

Reputation: 2182

Get names of tables where column doesn't exist

In MySQL, what query do I use to get the names of tables where a given column name doesn't exist yet? I have a database where some tables have a deleted_at column and some don't, and I want to have the names of the tables that don't have the deleted_at column yet.

To prevent confusion, I don't want to check from one single table of it exists, I want a list of all tables that don't have the column. Thanks for help!

Upvotes: 0

Views: 935

Answers (2)

Bernd Buffen
Bernd Buffen

Reputation: 15057

you can do it with the information schema like this: you must only the schema (cc)

SELECT * FROM information_schema.Columns
WHERE TABLE_NAME NOT IN (
    SELECT TABLE_NAME FROM information_schema.Columns
    WHERE COLUMN_NAME = 'deleted_at'
    AND TABLE_SCHEMA = 'cc'
    GROUP BY TABLE_NAME
  )
AND TABLE_SCHEMA = 'cc'
GROUP BY TABLE_NAME;

sample

MariaDB [cc]> SELECT * FROM information_schema.Columns
    -> WHERE TABLE_NAME NOT IN (
    ->     SELECT TABLE_NAME FROM information_schema.Columns
    ->     WHERE COLUMN_NAME = 'xid'
    ->     AND TABLE_SCHEMA = 'cc'
    ->     GROUP BY TABLE_NAME
    ->   )
    -> AND TABLE_SCHEMA = 'cc'
    -> GROUP BY TABLE_NAME;
+---------------+--------------+------------+-------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+----------------+------------------+------------+----------------+---------------------------------+----------------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | ORDINAL_POSITION | COLUMN_DEFAULT | IS_NULLABLE | DATA_TYPE | CHARACTER_MAXIMUM_LENGTH | CHARACTER_OCTET_LENGTH | NUMERIC_PRECISION | NUMERIC_SCALE | DATETIME_PRECISION | CHARACTER_SET_NAME | COLLATION_NAME | COLUMN_TYPE      | COLUMN_KEY | EXTRA          | PRIVILEGES                      | COLUMN_COMMENT |
+---------------+--------------+------------+-------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+----------------+------------------+------------+----------------+---------------------------------+----------------+
| def           | cc           | first      | id          |                1 | NULL           | NO          | int       |                     NULL |                   NULL |                10 |             0 |               NULL | NULL               | NULL           | int(11) unsigned | PRI        | auto_increment | select,insert,update,references |                |
+---------------+--------------+------------+-------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+----------------+------------------+------------+----------------+---------------------------------+----------------+
1 row in set (0.02 sec)

MariaDB [cc]>

Upvotes: 1

Uueerdo
Uueerdo

Reputation: 15941

information_schema is useful for this kind of stuff.

SELECT t.table_name 
FROM INFORMATION_SCHEMA.TABLES AS t
LEFT JOIN INFORMATION_SCHEMA.COLUMNS AS c 
   ON t.table_schema = c.table_schema
   AND t.table_name = c.table_name
   AND c.column_name = 'deleted_at'
WHERE t.table_schema = 'my_schema'
   AND c.table_name IS NULL
ORDER BY t.table_name;

Upvotes: 3

Related Questions