Find all tables without a certain column name

So, I've seen lots of questions about finding all tables with a specific column name. However, I'm trying to find all tables WITHOUT a specific column. (In this case, EndDate). Is开发者_如何学运维 there a more elegant solution than just finding all the tables with that column, and comparing it to the list of all tables?

SELECT
    table_name
FROM
    INFORMATION_SCHEMA.TABLES T
WHERE
    T.TABLE_CATALOG = 'MyDB' AND
    T.TABLE_TYPE = 'BASE TABLE' AND
    NOT EXISTS (
        SELECT *
        FROM INFORMATION_SCHEMA.COLUMNS C
        WHERE
            C.TABLE_CATALOG = T.TABLE_CATALOG AND
            C.TABLE_SCHEMA = T.TABLE_SCHEMA AND
            C.TABLE_NAME = T.TABLE_NAME AND
            C.COLUMN_NAME = 'EndDate')

Try the following, It's standard SQL (and will work for almost every platform)

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
EXCEPT
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = 'EndDate'

Just as you suggested, you can't really get anything that's simpler than this.

This should do it.

SELECT * FROM INFORMATION_SCHEMA.TABLES t
WHERE NOT EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS c 
   WHERE c.TABLE_NAME = t.TABLE_NAME AND c.TABLE_SCHEMA=t.TABLE_SCHEMA 
  AND c.COLUMN_NAME='EndDate')

I have many more schemas and tables than I really ought to, so I need an optimized version that runs in a second.

Why not get all tables, then get all tables with the field, UNION ALL these results, and just show the table names where HAVING COUNT(table_name) = 1? That's just what I did.

Notice the two vars to change below: YourSchemaHere and YourFieldHere.

SELECT TABLE_NAME, COUNT(TABLE_NAME) FROM (
    (SELECT DISTINCT c1.TABLE_NAME
        FROM INFORMATION_SCHEMA.COLUMNS c1
        WHERE c1.Table_Schema = "YourSchemaHere")
    UNION ALL 
    (SELECT DISTINCT c2.TABLE_NAME
        FROM INFORMATION_SCHEMA.COLUMNS c2
        WHERE c2.Table_Schema = "YourSchemaHere" AND
        c2.Column_Name = "YourFieldHere")
) x GROUP BY TABLE_NAME
HAVING COUNT(TABLE_NAME) = 1;

Online Demo at SQL Fiddle: http://sqlfiddle.com/#!9/791dde/1/0

Explanation of my approach:

• Get all tables with : SELECT DISTINCT c1.TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS c1 WHERE c1.Table_Schema = "YourSchemaHere"
• Get all tables with column : SELECT DISTINCT c2.TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS c2 WHERE c2.Table_Schema = "YourSchemaHere" AND c2.Column_Name = "YourFieldHere"
• UNION ALL these two results.
• Items in resulting list will be duplicated if they have the column, but only appear once if they do not.
• Select only those without the field:HAVING COUNT(*) = 1