1

How to find tables in which specific column not exists. E.g column ID not present in table Contact, then Contact table will be in result set.

I tried:

SELECT DISTINCT t.name
FROM sys.tables t
INNER join sys.columns C ON c.object_id = t.object_id
WHERE c.name <> 'ID'

But above query return all table for which column ID is present in it.

Christos
  • 53,228
  • 8
  • 76
  • 108
Ankush Madankar
  • 3,689
  • 4
  • 40
  • 74
  • Try the [link](http://stackoverflow.com/questions/133031/how-to-check-if-column-exists-in-sql-server-table) if it helps – Abhi Jan 09 '15 at 11:11
  • @Abhi Mention use to find those table which having specific column in it. But in this case I required exact opposite of it. – Ankush Madankar Jan 09 '15 at 11:13
  • The query works for me, do you want column name having value "ID" or containing "ID" like EmpID? – Adil Jan 09 '15 at 11:18

6 Answers6

1

Can u try this query,

SELECT * FROM sys.tables WHERE type = 'U' AND object_id NOT IN (
SELECT DISTINCT c.object_id FROM sys.columns c WHERE c.name = 'ID')
Jithin Shaji
  • 5,893
  • 5
  • 25
  • 47
1
SELECT  name
FROM    sys.Tables
WHERE   Object_ID NOT IN (SELECT Object_ID FROM sys.Columns WHERE name = 'ID')
Vishal Gajjar
  • 1,009
  • 9
  • 20
1

You don't need to perform a join, you can just add a subquery that identifies tables that have an ID column and exclude the object_id's using NOT IN(..the subquery..):

SELECT DISTINCT t.name
FROM sys.tables t
WHERE t.object_id NOT IN (SELECT object_id FROM sys.columns WHERE name = 'id')

The reason your query doesn't work is because you are simply getting all columns that aren't = ID and each table has many columns that match that criteria so they will be returned even if there is an ID column.

Tanner
  • 22,205
  • 9
  • 65
  • 83
1

One method is with NOT EXISTS:

SELECT  OBJECT_SCHEMA_NAME(t.object_id) AS SchemaName
      , t.name AS TableName
FROM    sys.tables t
WHERE   NOT EXISTS ( SELECT *
                     FROM   sys.columns c
                     WHERE  c.object_id = t.object_id
                            AND c.name = 'ID' );
Dan Guzman
  • 43,250
  • 3
  • 46
  • 71
1

Try the following, lists all table names NOT having ID.

SELECT   tableName 
FROM     (
          SELECT DISTINCT
                  t.name AS tableName
                 ,c.name AS columnName
          FROM    sys.tables t
          INNER JOIN sys.columns C
                  ON c.object_id = t.object_id
         ) AS t
WHERE columnName NOT IN( 'ID')
GROUP BY tableName
Mez
  • 4,666
  • 4
  • 29
  • 57
0

If you down't want to see system tables, then..

SELECT DISTINCT OBJECT_NAME(OBJECT_ID) TableName FROM SYS.COLUMNS WHERE NAME <> 'id' 
and OBJECTPROPERTY (OBJECT_ID, 'IsUserTable') = 1

EXCEPT

SELECT DISTINCT OBJECT_NAME(OBJECT_ID) TableName FROM SYS.COLUMNS WHERE NAME = 'id'
SouravA
  • 5,147
  • 2
  • 24
  • 49
  • I am not the downvoter but this query will not return the results @Ankush_Madankar requested. This query will include tables with the id column if the table has more than one column. – Dan Guzman Jan 09 '15 at 11:27
  • So you mean that if a table has only one column and that is `id`, it will not show up? – SouravA Jan 09 '15 at 11:30
  • Query will return same result as my query in result, because for single table multiple columns exists hence result will having condition for those column. You can check in sql server to clear it more. – Ankush Madankar Jan 09 '15 at 11:33
  • Yes. The query will work if the table has only the id column but erroneously return tables that have the id column plus others. The requirement @Ankush_Madankar specified is to return tables that don't have the specified column at all. – Dan Guzman Jan 09 '15 at 11:34
  • Query fetch some system tables and garbage temp tables also. – Ankush Madankar Jan 09 '15 at 12:15
  • Pls check again @AnkushMadankar – SouravA Jan 09 '15 at 13:08