1

I want to query and see which table do not have a column called hello.

I first use a query from this question: Find all tables containing column with specified name - MS SQL Server

SELECT      c.name  AS 'ColumnName'
            ,t.name AS 'TableName'
            ,c.object_id
FROM        sys.columns c
JOIN        sys.tables  t   ON c.object_id = t.object_id
WHERE       t.name in (select colname from someTable) -- table with one column containing table names.

So this gives me a table in long format i.e. every table has many rows. Now I want figure out which table does not have a column called hello.

xhr489
  • 1,957
  • 13
  • 39
  • 1
    Did you already try to put this query into a subquery / cte and perform a query on sys.tables t2 with a left join to above mentioned subquery? Every row with NULL result in this join should be a match for a table without the mentioned column. – Tyron78 Nov 09 '20 at 12:49
  • @Tyron78 no but I will try :-) – xhr489 Nov 09 '20 at 12:50
  • and I noticed a bug in the query: I guess the last line should be `WHERE c.name IN` instead of `WHERE t.name IN` - otherwise you will try to match table name against column name – Tyron78 Nov 09 '20 at 12:53
  • 1
    @Tyron78. No it is a list of tables that I want to check and that is what the subquery is doing. I have a table with table names... – xhr489 Nov 09 '20 at 12:55

1 Answers1

1

Here an example for the query with a cte. However, you have to consider that the same table name might exist in different schemas, so if you work with schemas you should add a join to sys.schemas and include this in your query:

WITH cteColTabs AS(
SELECT  t.name AS TableName
  FROM  sys.columns c
  JOIN  sys.tables  t   ON c.object_id = t.object_id
  WHERE t.name in (select colname from someTable) -- table with one column containing table names.
    AND c.name = N'hello'
)
SELECT t.Name AS TableName 
  FROM sys.tables  t
  LEFT JOIN cteColTabs ct ON ct.TabName = t.Name
  WHERE ct.TableName IS NULL
Tyron78
  • 4,117
  • 2
  • 17
  • 32