I'm wondering if someone can assist me with this script I wrote.

The logic behind is to first list the tables within the database.
Then query those tables for a specific column name and update that column field.

So it is in essence a list, search and update script.

Can anyone look at it and see if there is a better way for performance and
effiency? The database that this will run against will consist data that is at least a million records. So I am concern with down time and how well it run.

Script is below:

/**Get a list of tables **/

Declare my_curs scroll insensitive cursor For

Select o.name
from sysobjects o, syscolumns c
where o.type = 'U'
and o.id = c.id
and c.name = 'Facility'


Declare @n char(30), @cmd char(200), @msg char(30)

Open my_curs


/** loop through the list **/

Fetch Next From my_curs into @n

While @@fetch_status = 0

Begin
Select @cmd = "update " + @n + " set facility = 'W'"
Exec( @cmd)
Select @msg


/** Keep the transaction log from filling up **/

Declare cleanlog_curs Scroll Cursor
For Select *
From sysobjects o, syscolumns c
Where o.Type = 'U' and o.id = c.id
and c.name = 'Facility'

For Update of Facility

Open cleanlog_curs

Fetch Next From cleanlog_curs
/*********************************************/
Begin
print ""
select @n = "Updated Table" + RTRIM(UPPER(@n))
print ""
End

Deallocate cleanlog_curs

Fetch Next From my_curs into @n
End

Deallocate my_curs

Select o.name, c.name
from sysobjects o, syscolumns c
where o.type = 'U'
and o.id = c.id and c.name = 'Facility'