|
1 |
originally posted 2014-12-22 |
This example changes the value of the [Namespace] field for all records of all tables where the column is found.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 |
:setvar DB "foo" :setvar OLDNAMESPACE "foo" :setvar NEWNAMESPACE "bar" use $(DB) GO ALTER DATABASE $(DB) SET SINGLE_USER WITH ROLLBACK IMMEDIATE; select 'set database to single-user mode' GO exec sp_MSforeachtable ' alter table ? disable trigger all; alter table ? nocheck constraint all;' select 'disable triggers and constraints' GO begin try begin transaction print 'begin transaction' declare @sql nvarchar(max) select top 1000000 @sql = substring( ( select --'select distinct [Table] = ''' + t.[Name] + ''',[Namespace] from [' + t.[Name] + '];' 'update [' + t.[Name] + '] set [Namespace] = ''$(NEWNAMESPACE)'' where [Namespace] = ''$(OLDNAMESPACE)'';' from sys.tables t inner join sys.columns c on c.object_id = t.object_id where t.type = 'U' and c.[Name] = 'namespace' order by t.name for xml path('') ) ,1,1000000) --select @sql exec(@sql); commit transaction select 'commit transaction' end try begin catch rollback transaction print 'rollback transaction' select 'error : ' + error_message() end catch --enable triggers and constraints for the db exec sp_MSforeachtable ' alter table ? enable trigger all; alter table ? check constraint all;' GO select 'enable triggers and constraints' ALTER DATABASE $(DB) SET MULTI_USER; select 'set to multi-user' GO |