Wednesday, September 30, 2020

 For sp_MSForEachDB:
When the DB is exist, table exist, but column not exist. even you filter out the DB, but it still show the Error
"Msg 207, Level 16, State 1, Line 5 invalid column name"

eg. you have 2 DB (DB1 & DB2), Both DB same table name (TB1), but TB1 in DB1 have ColumnA, TB1 in DB2 dont have.

Code:
exec sp_MSForEachDB 'use [?]
if ''?'' <> ''DB2'' select * from ?.dbo.TB1 where ColumnA=''A'';'


You can see script was filter out the DB2, but it still give the error when you run it. 

----------------------
A poor solution here:

exec sp_MSForEachDB 'use [?]
if ''?'' <> ''DB2''
Begin
if exists (select COL_NAME(OBJECT_ID(B.name),C.column_id) from ?.sys.tables B join ?.sys.columns C on B.object_id=C.object_id where B.name=''TB1'' and C.name=''ColumnA'')
    Begin
    declare @colname varchar(100)
    declare @sql nvarchar(2000)
    set @colname=''ColumnA''
    select @sql = N''select * from ?.dbo.TB1 where ''+@colname+''=''''A'''';''
    exec sp_executesql @sql
    End
End'