CODEWITHSUNDEEP
sqlsql-serversp-msforeachdb
Jeremiah Nelson
Jeremiah Nelson

Reputation: 89

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

Upvotes: 1

Views: 1143

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269763

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.

Upvotes: 1

Martin Cairney
Martin Cairney

Reputation: 1767

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.

Upvotes: 0

Related Questions