Update a column present in multiple tables

Question

How to update a column which is present in multiple tables?

I am designing a DW having multiple Dim_tables with common column named DS how to set its value to 1 ?

i tried this but it gives error

use [DW]
go
exec sp_msforeachtable 'UPDATE ? SET [DS]=1'
go

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

What did you do to find the problem? What does the message tell you? — usr, Apr 11 '14 at 11:07
See this: http://stackoverflow.com/questions/9679997/sql-server-sp-msforeachtable-usage-to-select-only-those-tables-which-meet-some-c — dean, Apr 11 '14 at 11:09
@usr: its throwing same error evry time i try like _invalid column name_ — vhadalgi, Apr 11 '14 at 11:14

score 1 · Accepted Answer · answered Apr 11 '14 at 11:32

ok i got it ! the error is because the code is compiled before you check for the column. I could do like this instead :

EXEC sp_MSforeachtable 
@command1='
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=PARSENAME("?",2) AND TABLE_NAME=PARSENAME("?",1) AND COLUMN_NAME="DS")
BEGIN
   EXEC(''
          BEGIN
              UPDATE ? SET [DS]=1
          END
        '')
END
'

Update a column present in multiple tables

1 Answers1