Invalid column name using sp_MSForEachDB in SQL Server 2016

Question

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]

score 1 · Accepted Answer · answered Jan 19 '21 at 13:19

1

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.

answered Jan 19 '21 at 13:19

Gordon Linoff

1,242,037
58
646
786

I changed your code slightly and it works perfectly. If I am understanding the fix properly, then the procedure was not properly adding the correct database to the query? ***** ```code 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]'' SET @sql = REPLACe(@sql, ''[db]'', ''[?]''); EXEC sp_executesql @sql; END; END'; ``` – Jeremiah Nelson Jan 26 '21 at 18:58
@JeremiahNelson . . . Not exactly. The change is that the code is dynamic SQL so it is compiled when it is executed. That prevents the error you are seeing. – Gordon Linoff Jan 26 '21 at 20:52

score 0 · Answer 2 · answered Jan 19 '21 at 06:32

0

I'm going to guess it is permissions on the metadata for one or more of the databases.

The visibility of the metadata in information schema views is limited to securables that a user either owns or on which the user has been granted some permission. For more information, see Metadata Visibility Configuration.

It may be the specific permission that your login then has on that table that restricts whether you can see the column names. VIEW DEFINITION permission I think will be required so that this error isn't shown.

answered Jan 19 '21 at 06:32

Martin Cairney

1,714
1
6
18

Would this be case if I can query the tables individually without issue? Also, while I do not have admin access to the server or instance, I do have db_owner permissions to the databases themselves. – Jeremiah Nelson Jan 19 '21 at 06:36
I would expect that "should" probably allow you to then see the info. Can you query ```SELECT * FROM .INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'man_days' AND COLUMN_NAME = 'servicetype'``` for each database directly and get the correct result? – Martin Cairney Jan 19 '21 at 06:39
Yes, your test returns a row of data. I also used the example from the link you gave and was able to create and run the following stored procedure without issue. ```CREATE PROCEDURE assumes_caller_can_access_metadata AS BEGIN SELECT name, object_id FROM sys.objects WHERE name = N'man_days'; END; GO ``` – Jeremiah Nelson Jan 19 '21 at 06:48
Interestingly, if I query the entire table via * in my same original code, it works perfectly. Its only gives the error if I tell it to specifically grab servicetype. – Jeremiah Nelson Jan 19 '21 at 06:52
Do any of your databases use a Case Sensitive collation? And if so, is the column name always all lower case? – Martin Cairney Jan 19 '21 at 07:05
None of the databases use Case Sensitive collation. As a double check, I just verified that all the columns were all lower case. As an additional test I just cycled though all databases individually using the exact same code as posted and they all get the same error. ```EXEC sp_MSForEachDB 'USE [?]; IF DB_NAME() IN (''WestCoast'') BEGIN SELECT top 1 ''[?]'' as x, [servicetype] from [?].dbo.man_days END ' ``` – Jeremiah Nelson Jan 19 '21 at 07:20
Odd. Without having access to your databases I can't think of anything else to check for. I assume ```EXEC sp_MSForEachDB ' BEGIN IF EXISTS (SELECT * FROM [?].INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ''man_days'' AND COLUMN_NAME = ''servicetype'') SELECT top 1 [servicetype] from [?].[dbo].[man_days] END ' ``` also errors? There isn't a [man_days] table in a different schema is there? – Martin Cairney Jan 19 '21 at 07:38
Yes it fails. Thanks for trying. – Jeremiah Nelson Jan 19 '21 at 08:50
My only other suggestion is to configure an Extended Events trace on each DB to capture the actual T-SQL executed and see if there is any odd behaviour coming through that would explain it. – Martin Cairney Jan 20 '21 at 06:59

Invalid column name using sp_MSForEachDB in SQL Server 2016

2 Answers2