CODEWITHSUNDEEP
sql-servert-sql
Brian Kalski
Brian Kalski

Reputation: 957

Update value in all tables that have a column name in SQL Server

I am writing a stored proc that needs to search a database for all tables that have a certain column name. Once I get a list of tables that have that column I need to update a value in that column. So first I get a list of tables that have a certain column. 

SELECT      c.name  AS 'ColumnName'
            ,t.name AS 'TableName'
FROM        sys.columns c
JOIN        sys.tables  t   ON c.object_id = t.object_id
WHERE       c.name LIKE 'company' 
ORDER BY    TableName

Now that I have a list of tables that need to be updated I need to run a query similar to the following to update the data in each table. 

update table1 set company = @newValue where company = @oldvalue

I'm not sure how to go about writing this part. My first thought was to write a dynamic update statement inside of a cursor like:

Declare @newValue
Declare @oldValue
SET @companyCursor = CURSOR FOR
SELECT      t.name AS 'TableName'
FROM        sys.columns c
JOIN        sys.tables  t   ON c.object_id = t.object_id
WHERE       c.name LIKE 'company' 

OPEN @companyCursor;

FETCH NEXT FROM @companyCursor INTO @tableName;

WHILE @@FETCH_STATUS = 0
BEGIN
 update @tableName set company = @newValue where company = @oldValue
 FETCH NEXT FROM @companyCursor INTO INTO @tableName;
END

Is this a good strategy?

Upvotes: 1

Views: 2658

Answers (2)

Sean Lange
Sean Lange

Reputation: 33581

I really dislike cursors so even in cases like this where a cursor is a viable solution I like to leverage the system views to avoid looping. You still have to use dynamic sql because object names cannot be parameterized.

Please note that I am guessing the datatype for company here and you can change this easily. Make sure you change the variable definition both in your script AND in the dynamic sql. You entire script could be shortened to something like this.

declare @SQL nvarchar(max) = ''
    , @newValue varchar(10) = 'new'
    , @oldValue varchar(10) = 'old'

select @SQL = @SQL + 'Update ' + quotename(object_name(c.object_id)) + ' set company = @newValue where company = @oldValue;'
from sys.columns c
where c.name = 'company'

select @SQL

--uncomment the line below when you are satisfied the dynamic sql is correct.
--This dynamic sql is parameterized as much as possible
--exec sp_executesql @SQL, N'@newValue varchar(10), @oldValue varchar(10)', @newValue = @newValue, @oldValue = @oldValue

Upvotes: 2

benjamin moskovits
benjamin moskovits

Reputation: 5458

No the update at the end will not work. You need to use exec (@sql) like this:

declare @sql varchar(4000)
begin
    set @sql = 'update ' +  @tableName  + 'set company = ' +          @newValue +  'where company = ' +   @oldValue 
    exec (@sql)
   fetch next ...
end

This assumes that @newvalue and @oldvalue are being assigned values somewhere.

Upvotes: 1

Related Questions