-
Sample Script
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'
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|