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
'
Hmmm . . . I think the issue might be a compilation issue. Try this rather alternative
That is, turn the
SELECT
into dynamic SQL, so it is not evaluated at the same time as theIF
.