Notifications
Sign Up Sign In
Q&A

How do I find all the tables in a database that don't have a specific column?

+0
−1

I need to add one column with the same name to all of the tables in my database, how can I find which tables don't currently have a column with that name?

Why should this post be closed?

0 comments

1 answer

+1
−0

The select for finding tables without a specific column name is,

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 ='COLUMN_NAME'
    AND TABLE_SCHEMA='DATABASE_NAME'
);

0 comments

Sign up to answer this question »