Help With Cursors

Question

Hi all, here’s my task:

From one database I have to find all the tables with server name. I want to be able to do exec sp_chgname with the parameter being the new name. But right now I just need to get my cursor right!

DECLARE @TableName TABLE
(New_Name varchar(255),
Column_Name varchar(255))
--DECLARE @TableName varchar(255)
--DECLARE @column _Name varchar(255)

DECLARE TableCursor CURSOR FOR
select table_name, column_name from information_schema.columns
where table_catalog = 'mgmt_tester'
and column_name like '%sname%' or column_name like '%server_name%' or
column_name like '%servername%'

OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @TableName

WHILE @@Fetch _STATUS = 0

BEGIN

update @TableName set Column_Name = 'TEST'

FETCH NEXT FROM TableCursor INTO @TableName

END

CLOSE TableCursor

DEALLOCATE TableCursor

The goal is to go and find every table that has the server name. Most tables have sname for that column. But there are a few tables that have the column as servername or server_name.

So I figured iterate through all the tables with those columns. Then update them to ‘test’…for right now I just want to see if I can update the values to ‘test’.

I hope this makes sense because it’s getting foggy for me!

Answer 1

Please try use the parenthesis to separate the OR conditions:

select table_name, column_name from information_schema.columns
where table_catalog = 'mgmt_tester'
and ( column_name like '%sname%' or column_name like '%server_name%' or
column_name like '%servername%' )

Answer 2

The table name in an UPDATE statement cannot be a variable.

You must use "dynamic sql" to do this kind of operation.

DELCARE @sqlcmd VARCHAR(max);
SET @sqlcmd = 'UPDATE ' + @tablename + ' SET Column_Name = ''TEST'''

EXEC (@sqlcmd)

Answer 3

Try replacing the UPDATE line with this statements:

declare @c varchar(max) = concat( quotename(@TableName), '.', quotename(@ColumnName))
exec sp_rename @c, 'test', 'COLUMN'

where @ColumnName is the current name of the column to be renamed (extracted from INFORMATION_SCHEMA.COLUMNS).

Answer 4

I think we have discussed this before. Just have one UPDATE statement per table. That will be a lot easier to maintain. Not the least when the column name is not consistent between tables.

Answer 5

Hi @cipher7836 ,

Please refer to the suggestions of other experts to modify your cursor.If there are still problems, please provide error information.

In addition, the cursor is only used as a backup method. It is used when WHILE loops, subqueries, temporary tables, table variables, custom functions or other methods cannot implement certain queries.

Regards,
Echo

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.