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
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
.