SQL Server method to add a field to multiple tables

1. Using Cursors

DECLARE @sql varchar ( $), @name varchar ( +) DECLARE my_cursor scroll cursor for    SelectName fromsysobjectswhereType ='u'and name like'jobs_%'Open My_cursorfetch Next frommy_cursor into @name while(@ @fetch_status =0) BEGIN Print'processing'+@nameSet@sql = N'ALTER TABLE Testbfcmisuser.'+ @name + N'Add note varchar ($)'exec (@sql) print'finished'FETCH Next frommy_cursor into @nameendclose my_cursordeallocate my_cursor

2. Using temporary tables

DECLARE @sql varchar ( $), @name varchar ( -), @countintSelectIdentityint,1,1) asID, name into #JobTable fromsysobjectswhereType ='u'and name like'jobs_%'Set@count =@ @rowcount while@count >0beginSelectTop1@name =name from#JobTablewhereID =@count Print'processing'+@nameSet@sql = N'ALTER TABLE Testbfcmisuser.'+ @name + N'Add note varchar ($)'exec (@sql) print'finished'        Set@count = @count-1Enddrop Table #JobTable


Http://www.wonima.com Oh, funny Web

SQL Server method to add a field to multiple tables