Geek Answers Handbook

Find all tables without a specific column name

So, I saw a lot of questions about finding all tables with a specific column name. However, I am trying to find all tables WITHOUT a specific column. (In this case, EndDate). Is there a more elegant solution than just finding all tables with this column and comparing it with a list of all tables?

+8
sql sql-server
source share
3 answers
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')
+10
source share

That 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')
+1
source share

Try the following: this is standard SQL (and will work on almost every platform)

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

As you expected, you cannot get anything simpler than this.

0
source share

More articles:


All Articles