Search code examples
sql-servert-sqlexceptssms-16

Loop through multiple tables to see if a column exists?


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

Solution

  • 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'