Loop through multiple tables to see if a column exists?

Question

I have a temp table that holds a list of table names I need to loop through these tables to see if this column exists. If not then print out the table where it doesn't exist

So far I have

CREATE TABLE #ListOfTables (
   [TableName] varchar(max)
)
INSERT INTO #ListOfTables
   ([TableName])
  (SELECT TableName from dbo.CustomTableAttributes)

-- Statement
DECLARE @stm nvarchar(max)
SET @stm = N''

IF EXISTS(SELECT 1 FROM sys.columns 
          WHERE Name = N'BegDt'
 Errors Here --> AND Object_ID = Object_ID(N''+ ***Select [TableName] FROM #ListOfTables*** +''))

Begin
'Do Work here'
End

This seems overly complicated. You should be able to join to the system tables and column list to return results where dont have that column name in the table list from system tables — Brad, Mar 14 '19 at 17:50
If you only need to print the value of the tables, why not use `FOR XML PATH` or `STRING_AGG` to generate the list and set the value to a variable, and then print the value of the variable? — Thom A, Mar 14 '19 at 17:52

score 2 · Accepted Answer · answered Mar 14 '19 at 17:58

Please try something like this

Exist:

SELECT table_name 
    FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE table_name in (SELECT TableName from dbo.CustomTableAttributes)
    AND column_name = 'BegDt'

does not Exist:

SELECT TableName from dbo.CustomTableAttributes
Except    
SELECT table_name 
    FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE table_name in (SELECT TableName from dbo.CustomTableAttributes)
    AND column_name = 'BegDt'

score 1 · Answer 2 · answered Mar 14 '19 at 17:56

This should return all the tables that do not have a column in the table with a name of 'ID'. Just change the 'ID' to what you need.

SELECT * 
FROM sys.tables TBL
-- this reverses the logic and returns all tables that do not exist in the below list
WHERE TBL.object_id NOT IN (
    -- this selects all the tables that have that column in the table structure
    SELECT TBL.object_id
    FROM sys.tables TBL
    INNER JOIN sys.columns COL ON TBL.object_id = col.object_id
    WHERE col.name = 'ID'
)

Loop through multiple tables to see if a column exists?

2 Answers2