?
Solved

Update a column throughout the DB

Posted on 2013-05-24
5
Medium Priority
?
259 Views
Last Modified: 2013-05-28
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.
0
Comment
Question by:Easwaran Paramasivam
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 25

Expert Comment

by:jogos
ID: 39196382
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/
0
 
LVL 25

Expert Comment

by:jogos
ID: 39196390
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

0
 
LVL 16

Author Comment

by:Easwaran Paramasivam
ID: 39196532
It gives error as Incorrect syntax near 'schema_id'.
0
 
LVL 16

Accepted Solution

by:
Surendra Nath earned 2000 total points
ID: 39196548
step 1: execute the below update statement, it gives you a set of update statements as rows...
step 2: Now, copy them up and put them back in SSMS and execute them.

select 'UPDATE ' + T.TABLE_SCHEMA + '.' + T.TABLE_NAME + ' SET DataRowVersion = 1 where DataRowVersion is null ; ' 
from INFORMATION_SCHEMA.TABLES T
JOIN INFORMATION_SCHEMA.COLUMNS C
ON T.TABLE_NAME = C.TABLE_NAME
AND c.COLUMN_NAME ='DataRowVersion'

Open in new window

0
 
LVL 16

Author Closing Comment

by:Easwaran Paramasivam
ID: 39200920
Thanks.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question