CODEWITHSUNDEEP
mysqlsqlselectinformation-schema
wrangler
wrangler

Reputation: 2035

Select tables that do not contain a certain column in MySQL

I am trying to select all tables that do not have column named 'unique'. I can select all tables that have it using:

SELECT DISTINCT TABLE_NAME 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = 'unique'
AND TABLE_SCHEMA ='database';

Is there a simple way to modify the SELECT statement so it lists all tables that do not have that column? This seems like it would be simple, but I can't figure it out, and can't find an answer on the forum.

Thanks

Upvotes: 4

Views: 5953

Answers (4)

benmutinda
benmutinda

Reputation: 21

The above queries work, if only one database exists in the server. If not, tables from other databases will also be listed. Consider this simplified version that picks tables from a particular DB. All in all, this is a great highlight!

SELECT DISTINCT TABLE_NAME 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_SCHEMA = 'DATABASE_NAME' 
AND TABLE_NAME NOT IN (SELECT DISTINCT TABLE_NAME 
                            FROM INFORMATION_SCHEMA.COLUMNS 
                           WHERE COLUMN_NAME = 'FIELD_NAME' 
                             AND TABLE_SCHEMA ='DATABASE_NAME');

Upvotes: 2

canon
canon

Reputation: 41665

select
t.*
from INFORMATION_SCHEMA.TABLES as t
    left join INFORMATION_SCHEMA.COLUMNS as c
    on c.TABLE_NAME = t.TABLE_NAME
    and c.TABLE_SCHEMA = t.TABLE_SCHEMA
    and c.COLUMN_NAME = 'unique'
where c.COLUMN_NAME is null
and t.TABLE_SCHEMA = 'database'

Upvotes: 8

fredley
fredley

Reputation: 33871

A simple inversion:

SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME NOT IN

(SELECT DISTINCT TABLE_NAME 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = 'unique'
AND TABLE_SCHEMA ='database');

Upvotes: 0

Lamak
Lamak

Reputation: 70638

SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME NOT IN (
SELECT DISTINCT TABLE_NAME 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = 'unique'
AND TABLE_SCHEMA ='database')

Upvotes: 0

Related Questions