T-SQL Check if the database contains certain tables and columns MSSQL

Question

My goal is to continue in the procedure only with the database selected if it contains a specific table and a specific column.

So that I don't get an error message later when selecting that this column doesn't exist.

Background: It is the case that database A table A has a more current status than database B with table A. In database B of table A a column is missing, which is the reason for the error that this column does not exist. Which I also do not want to add.

This is my attempt so far:

exec sp_MSforeachdb 
'
use [?]
IF (''?'' NOT LIKE ''%example%'' AND ''?'' NOT LIKE ''%example_two%''
    AND EXISTS(Select 1 from sys.tables where name = ''Table1'') 
    AND EXISTS(Select 1 from sys.tables where name = ''Table2'')
    AND ''?'' NOT IN (SELECT * FROM Database.dbo.Blacklist)
    )
BEGIN
    IF(EXISTS(SELECT myColumn FROM Table1 Where ID = 5 AND XYZ = 3)) BEGIN.....'

Even when i switch instead of

..
    AND EXISTS(Select 1 from sys.tables where name = ''Table1'') 
    AND EXISTS(Select 1 from sys.tables where name = ''Table2'')
..

To:

SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = ''Table1''
AND COLUMN_NAME = ''MyExample''

it will not work the error log outputs: Incorrect syntax near 'TableXX'. The error log gives me all databases that are practically checked, system databases as well.

Alternative it would also be helpful if someone knows how to use a case when in the select by trying to store an alternative value once the column does not exist e.g. like this:

  'SELECT...
   CASE WHEN exists(
           SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE 
           TABLE_NAME =''TableX'' AND COLUMN_NAME = ''ColumnX'') 
        THEN ''ST.ColumnX''
        ELSE ''0 AS ColumnX''
        END
        FROM ...'

I just want to select databases that have the valid table and column, whenever I don't exist I take another table, this works until I find a table where a column doesn't exist, the column is however my select statement therefore I get an error, I want to focus on the alternative question, is there a way to check if the column exists in the current table? before assigning a value?

Case WHEN ColumnX exists THEN (ValueOfColumnX) ELSE 0 END AS Column.

Thank you in advance for any help

Not with you - you have a stored procedure to which you pass a db name which builds a dynamic sql query and you want to abort/quit the sp if a table and/or column in the selected db is missing (without notification) a simplified example of the sp would be nice. — P.Salmon, Apr 29 '22 at 11:28

score 0 · Accepted Answer · answered Apr 30 '22 at 02:12

Use one single quotes when you are specifying the names of your tables and columns

SELECT 1 FROM your_databasename.INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = 'Table1'
AND COLUMN_NAME = 'MyExample'

If you want to check if a column exists in a table try looking into this thread

T-SQL Check if the database contains certain tables and columns MSSQL

1 Answers1