Applying sp_msforeachtable for selected tables

I have a problem in updating some column values in a table. In my database there are many开发者_如何学Go tables and out of which I have to update those tables which is having a column by the name "Col1".

If I do

exec sp_msforeachtable 'UPDATE ? SET Col1=

case when Col1 = "ppp" then "qqq"
     when Col1 = "aaa" then  "xxx" 
end
' 

It will report an error message as

Msg 207, Level 16, State 1, Line 1 Invalid column name 'col1'.

What to do?

If you must use sp_msforeachtable you can conditionally run your code based on the existence of your column.

i.e.

exec sp_msforeachtable '
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMS WHERE COLUMN_NAME =''Col1'' AND TABLE_NAME = ''?'')
BEGIN
    UPDATE ? SET Col1=

    case when Col1 = "ppp" then "qqq"
     when Col1 = "aaa" then  "xxx" 
    end
END 
'

Or, better yet:

DECLARE @myScripts TABLE(
                    id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
                    sql VARCHAR(max)
                  )
DECLARE @max INT,
        @i   INT,
        @SQL VARCHAR(MAX)
INSERT INTO @myscripts(sql)
SELECT 'UPDATE [' + tablename + ']  SET Col1=
        case when Col1 = "ppp" then "qqq"
         when Col1 = "aaa" then  "xxx" 
        end'
    FROM [INFORMATION_SCHEMA].TABLES WHERE TABLE_NAME IN ('table1','table2','table3','table4')
SELECT @max = @@ROWCOUNT, @i = 1

WHILE @i <= @max
BEGIN
    SELECT @SQL = Script
    FROM @myScripts
    WHERE ID = @i

    EXEC sp_executesql @statement = @SQL
    SET @i = @i + 1

END 

exec sp_msforeachtable 
  @command1=
    '
    BEGIN
        UPDATE ? SET Col1=
            case when Col1 = "ppp" then "qqq"
             when Col1 = "aaa" then  "xxx" 
         END
    END 
    '
  @whereand='AND o.id in (select object_id from sys.columns c where c.name=''Col1'')'

The whereand will filter what gets sent to command, so you can execute command freely knowing that only tables with Col1 will be referenced.