July 8, 2003 at 2:09 pm
RE: SQL 7.0 on Windows 2000 Professional
I need to update the contents of a column which is found in 175 tables. Yes, 175 tables. I found the table names and ID #'s with:
SELECT SYSCOLUMNS.NAME, SYSCOLUMNS.ID,
sysobjects.name, SYSOBJECTS.ID
FROM SYSOBJECTS, syscolumns
WHERE SYSOBJECTS.ID = syscolumns.ID AND
SYSCOLUMNS.NAME = 'blah'
sysobjects.name and SYSOBJECTS.ID relate to the table name and syscolumns.name being the column name.
Since there are so many tables, I need to automate.
perhaps a stored procedure with a cursor to grab the next table ID which then passes to another cursor for the update?
The updated value of the column will be the same across all tables, ie 'abc'. This new value will replace a specific value so I'll need a "WHERE <syscolumns.name value> = 'ZXY'".
Any ideas on how best to accomplish this task?
Greg H
Greg H
July 9, 2003 at 12:45 am
you can do it easily using . dynamic sql.
to give you a pointer. herez some code.
eg:
declare @vsql varchar(8000)
declare @tablename varchar(300)
declare @fieldname varchar(300)
fetch next from yourcursor into @tablename,@fieldname
set @vsql='update ' + @tablename + ' set '+ @fieldname + ' where ' + @fieldname +'>=''ZXY''
exec (@vsql)
HTH
Edited by - nazim on 07/09/2003 12:47:36 AM
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply