sqlsql-serversp-msforeachdb

Invalid column name using sp_MSForEachDB in SQL Server 2016


I am attempting to query multiple databases housed on the same SQL Server instance using sp_MSForEachDB.

There are 8 databases that have the table man_days with a column named servicetype. I have manually verified that all 8 tables are identical.

When run the following query I get the error message Invalid column name 'servicetype'

EXEC sp_MSForEachDB 
'
BEGIN
    IF EXISTS (SELECT * FROM [?].INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ''man_days'' AND COLUMN_NAME = ''servicetype'')
        SELECT top 1 [man_days].[servicetype] from [?]..[man_days]
END
'

The result set is as expected however the error keeps coming up. What am I doing wrong?

Edit... If I change the code to query all columns as in the code below, it works without issue. Or if I change it to query other single columns within that table it works without issues. It only fails when I attempt to select that one column

EXEC sp_MSForEachDB 
'
BEGIN
    IF EXISTS (SELECT * FROM [?].INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ''man_days'' AND COLUMN_NAME = ''servicetype'')
        SELECT top 1 * from [?]..[man_days]
END
'

[Result Set] [1]Error Message


Solution

  • Hmmm . . . I think the issue might be a compilation issue. Try this rather alternative

    EXEC sp_MSForEachDB 
    '
    BEGIN
        IF EXISTS (SELECT * FROM [?].INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ''man_days'' AND COLUMN_NAME = ''servicetype'')
        BEGIN
            DECLARE @sql NVARCHAR(MAX);
            SET @sql = ''SELECT top 1 [man_days].[servicetype] from [db]..[man_days]'';
            REPLACE(@sql, ''[db]'', ?);
            EXEC sp_executesql @sql;
        END;
    END
    ';
    

    That is, turn the SELECT into dynamic SQL, so it is not evaluated at the same time as the IF.