# Sunday, April 23, 2006
# Update multiple tables in SQL Server 2005

I was required to update 40+ tables in our DB; Moran Benisty, our DBA master gave me a cool solution:

SELECT 
   'update ' + name + ' set SomeColumn = ''SomeValue'' where OtherColumn = ''SomeOtherValueToMatchBy'''
FROM 
   sys.objects
WHERE 
   name LIKE 'LK_%'


Now all I had to do is copy the results and run them. Sweet !

BTW - use at your own risk. :)

SqlServer 2005
« What Great Interviewees Ought To Know  | Main |   Inconsistency of SqlParemeter from SqlDbType.DateType with Sql Server 2005 »

Posted by Oren Ellenbogen 
23/04/2006 04:42, Israel time UTC-07:00,     Comments [2]  | 
Tuesday, April 25, 2006 8:34:08 PM (Jerusalem Standard Time, UTC+02:00)
I use it quite a bit, this is a great trick.
The bad side of it is when you do it for production, recursively.
Ayende Rahien
Thursday, June 01, 2006 11:42:47 PM (Jerusalem Standard Time, UTC+02:00)
Don't forget sp_MSforeachtable

This helpful little guy "can" sometimes save the whole copy and paste bit:

e.g. EXEC sp_MSforeachtable 'DBCC DBREINDEX (''?'')'
Josh Robb
Comments are closed.