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'
No comments:
Post a Comment