Update with cursor

Question

Post reply

Update with cursor

Simone Foschi

Old Hand

Points: 328
More actions
May 12, 2008 at 7:49 am

#95929

Hello,
I am triyng to update all the columns in all the tables if i find a certain value but I can't do it on my own. This is the best i can achieve as it is the first time with cursors. But it has an error:
Must declare the table variable "@table_name".
Is this query possible?
There is another way?
SET NOCOUNT ON
DECLARE @table_name nvarchar(500), @column_name nvarchar(500) --big enough 🙂 but it doesn't care the db is not on production server
DECLARE vend_cursor CURSOR
FOR select TABLE_NAME, COLUMN_NAME from Information_schema.COLUMNS where TABLE_NAME in (SELECT TABLE_NAME FROM Information_schema.TABLES)
OPEN vend_cursor
FETCH NEXT FROM vend_cursor INTO @table_name, @column_name
WHILE @@FETCH_STATUS <> -1
BEGIN
UPDATE @table_name set @column_name = replace(@column_name,'','')
END
CLOSE vendor_cursor
DEALLOCATE vendor_cursor
Tanks all in advance

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jeremy-475548 Hall of Fame Points: 3065 More actions · Answer 1

You'll have to make it a dynamic query. That is, build your sql string, and execute it

declare @sql nvarchar(max)

Set @sql = 'update ' + @tablename + '...'

exec @sql

You might also want to look into the sp_msforeachtable as outlined here:

http://www.databasejournal.com/features/mssql/article.php/3441031

the__t Hall of Fame Points: 3260 More actions · Answer 2

You also need to make sure of two things:

1) You will need another statement FETCH NEXT FROM vend_cursor INTO @table_name, @column_name after your update statement and before the END statement. Otherwise you will be in a loop.

2) Be consistent with your cursor name. You have vend_cursor and vendor_cursor.

"Got no time for the jibba jabba!"
-B.A. Baracus

the__t Hall of Fame Points: 3260 More actions · Answer 3

Also, I don't think you need:

where TABLE_NAME in (SELECT TABLE_NAME FROM Information_schema.TABLES)

"Got no time for the jibba jabba!"
-B.A. Baracus

Simone Foschi Old Hand Points: 328 More actions · Answer 4

Thanks all for your reply.

I will try these tips, but not sure to can post the result as i don't know if is intention of my boss to spend time over the query. Think i must use multiple update queries 🙁 -->

Declare @script nvarchar(255)

Set @script = N'the damn script'

Update [articoli] Set [desc] = replace(Cast([desc] as nvarchar(max)), @script, ''))........

Tanks again

Simon