Link to home
Create AccountLog in
Avatar of Easwaran Paramasivam
Easwaran ParamasivamFlag for India

asked on

Update a column throughout the DB

In my database more tables contain a column called DataRowVersion. I would like to update the column value as 1 in all of the tables. How to perform it in single shot? Please assist.
Avatar of jogos
jogos
Flag of Belgium image

Find all tables that hold your column
http://blog.sqlauthority.com/2008/08/06/sql-server-query-to-find-column-from-all-tables-of-database/

use it as the base for a cursor whith in the cursorloop your update composed as a dynamic sql
http://www.softcodearticle.com/2012/12/mssql-declare-cursor-exampleexecute-sql-string/
Or execute this script.  Execute it with text output box and  copy output and past it in the query window to execute it.
I added the WHERE IS NULL, but you can change that for your needs
SELECT 'update ' +  t.name + ' set DataRowVersion = 1 where DataRowVersion is null ; '
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name ='DataRowVersion'
ORDER BY schema_name, table_name;

Open in new window

Avatar of Easwaran Paramasivam

ASKER

It gives error as Incorrect syntax near 'schema_id'.
ASKER CERTIFIED SOLUTION
Avatar of Surendra Nath
Surendra Nath
Flag of India image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Thanks.