--Ce script va parcourir l'ensemble des tables, colonnes et entrées de la base de donnée dans laquelle 
	--il s'éxécute (une base DotNetNuke par exemple) et va remplacer le contenu de la valeur de "@condition" 
	--par la valeur de "@replacestring".
	--Dans cet exemple, le script est utilisé pour migrer les URLs d'un site DotNetNuke, mais vous pouvez
	--utiliser n'importe quelle valeur (email, terme, faute d'orthographe, constitution rapide d'un wiki ...)
		
	--This script will browse the whole content of a given database (Table, Row, Entry only) - a DotNetNuke DataBase 
	--for instance - and replace the value of "@condition" by the value of "@replacestring".
	--In this example, the script is used to replace the URLs of a DotNetNuke website when you which to migrate to a 
	--new domain, from development instance to prod instance for example, but can be used for or lot of other scenarios
	--( Replace email, replace term, ...)	
	
	
	--Le nombre de clés primaire sur les tables de la base est limitté à 4. (Suffisant pour DotNetNuke)
	
	--Le script ne traitera pas les tables sans clé primaire. Il listera à la fin toute table sans PK.
	
	--Les champs < 4000 caractères seront transformés en nvarchar pour traitement puis retransformés 
	--dans leur type initial pour insertion car REPLACE ne traite pas plus de 4000 caractères 
	--(donc cas du nvarchar(4001 et +) non traité).
	
	--Les champs ntext sont mis à jour avec UPDATETEXT.
	
	--Attention! il y a deux rollback. Si vous voulez enregistrer les modifications de la base,
	--	   il faudra supprimer la transaction avant l'exécution de ce script.
	--	   Donc après les tests, commenter "begin tran" et "rollback tran" pour passer
	--	   à une éxécution effective (ligne 246 et 250 et ligne 319 et 323).

	
	
	
	--Ce texte est publié sous la licence Creative Commons Paternité-Partage des Conditions Initiales 
	--à l'Identique 3.0 Unported. http://creativecommons.org/licenses/by-sa/3.0/deed.fr
	--* Vous êtes libres : 
	--	de reproduire, distribuer et communiquer cette création au public.
	--	de modifier cette création.
	
	--* Selon les conditions suivantes :
	--	- Paternité
	--		Vous devez citer le nom de l'auteur original de la manière indiquée par l'auteur de 
	--		l'oeuvre ou le titulaire des droits qui vous confère cette autorisation (mais pas d'une 
	--		manière qui suggérerait qu'ils vous soutiennent ou approuvent votre utilisation de l'oeuvre).
	--	- Partage des Conditions Initiales à l'Identique
	--		Si vous transformez ou modifiez cette oeuvre pour en créér une nouvelle, vous devez la 
	--		distribuer selon les termes du même contrat ou avec une licence similaire ou compatible.
			

	--With the understanding that:
	--	- Waiver
	--		Any of the above conditions can be waived if you get permission from the copyright holder.
	--	- Public Domain
	--		Where the work or any of its elements is in the public domain under applicable law, that 
	--		status is in no way affected by the license.
	--	- Other Rights - In no way are any of the following rights affected by the license:
	--		- Your fair dealing or fair use rights, or other applicable copyright exceptions and limitations;
	--		- The author's moral rights;
	--		- Rights other persons may have either in the work itself or in how the work is used, such as publicity or privacy rights.

	--Notice
	--	A chaque réutilisation ou distribution de cette création, vous devez faire apparaître clairement au public 
	--	les conditions contractuelles de sa mise à disposition. La meilleure manière de les indiquer est un 
	--	lien vers cette page web.
	

BEGIN
	print 'Décommentez "begin tran" et "rollback tran" pour que le script s''execute réellement.'
	print 'Le script ne traitera pas les tables sans clé primaire listées ci-après.'
END

set nocount on 

declare @condition nvarchar(400) 
declare @replacestring nvarchar(400)

set @condition = 'www.dotnetnuke.fr' -- Ancienne URL
set @replacestring = 'www.dotnetnuke-fr.org' -- Nouvelle URL


-- Supprime les tables temporaires en cas de répétition sur une éxécution qui avait échoué.
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[t_Res]') AND type in (N'U'))
DROP TABLE t_Res
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_Applications]') AND type in (N'U'))
DROP TABLE t_ResExclu
GO

-- Tables de resultats temporaires (sera supprimée ensuite)
create table t_ResExclu ( table_name varchar(100) , column_name varchar(100))
create table t_Res ( table_name varchar(100) , column_name varchar(100) , Id1 varchar(100) , Id2 varchar(100) , Id3 varchar(100) , Id4 varchar(100) , res int , column_content varchar(8000) , column_lenght int ) declare @table_name varchar(100) declare @column_name varchar(100) declare @sqlSelect nvarchar(4000) declare @sqlUpdate nvarchar(4000) declare @nblen int declare @Id1 nvarchar(100), @Id2 nvarchar(100), @Id3 nvarchar(100), @Id4 nvarchar(100), @IdT nvarchar(100), @Clmn nvarchar(300), @ClmnNtext nvarchar(300), @Clmnfetch nvarchar(300), @ClmnUpText nvarchar(300), @JoinClmn nvarchar(300), @nbID smallint, @Nb_enr int declare table_list



-- Parcours l'ensemble des tables de la base de données sélectionnée
cursor FAST_FORWARD READ_ONLY
	for
		select table_name
		from information_schema.tables
		where table_type = 'BASE TABLE' 
		-- Activez le paramètre suivant pour restrindre la recherche/remplacement à une seule table
		--and  table_name = 'EventLog'

		open table_list fetch next
			 from table_list into @table_name 
				while @@fetch_status=0 begin
				 
				 set @ID1 = null
				 set @ID2 = null
				 set @ID3 = null
				 set @ID4 = null
				 set @Clmn = ''
				 set @ClmnNText = ''
				 set @ClmnFetch = ''
				 set @JoinClmn = ''
				 set @nbID = null
				 set @ClmnUpText = ''
	
				-- Compte les cles primaires des tables parcourues
				 select @nbID= count(c.column_name)
				 from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk 
					, INFORMATION_SCHEMA.KEY_COLUMN_USAGE c 
				 where pk.table_name = @Table_Name
				 and CONSTRAINT_TYPE = 'PRIMARY KEY'
				 and c.table_name = pk.table_name
				 and c.constraint_name = pk.constraint_name 

				if @nbID > 0 
					begin 
						declare IDlist
						 cursor FAST_FORWARD READ_ONLY
							 for
								-- Liste les cleés primaires
								 select c.column_name
								 from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk 
									, INFORMATION_SCHEMA.KEY_COLUMN_USAGE c 
								 where pk.table_name = @Table_Name
								 and CONSTRAINT_TYPE = 'PRIMARY KEY'
								 and c.table_name = pk.table_name
								 and c.constraint_name = pk.constraint_name 

								 open IDlist fetch next
								 from IDlist into @IdT 
									while @@fetch_status=0 
									begin 
										if @ID1 is null 
										begin
											 set @ID1 = @IdT
											 set @Clmn = ' ' + @IdT + ' Id1'
											 set @JoinClmn = ' ON T.' + @IdT + ' =  R.Id1'
											 set @ClmnUpText = @IdT + ' =  @Id1'
											 set @ClmnFetch = '@Id1' 
										end 
										else if @ID2 is null 
												begin
													 set @ID2 = @IdT
													 set @Clmn = @Clmn + ', ' + @IdT + ' Id2'
													 set @JoinClmn = @JoinClmn + ' And T.' + @IdT + ' =  R.Id2'
													 set @ClmnUpText = @ClmnUpText + ' AND ' + @IdT + ' =  @Id2'
													 set @ClmnFetch = @ClmnFetch + ', @Id2' 
												end 
										else if @ID3 is null 
													begin
														 set @ID3 = @IdT
														 set @Clmn = @Clmn + ', ' + @IdT + ' Id3'
														 set @JoinClmn = @JoinClmn + ' And T.' + @IdT + ' =  R.Id3'
														 set @ClmnUpText = @ClmnUpText + ' AND ' + @IdT + ' =  @Id3'
														 set @ClmnFetch = @ClmnFetch + ', @Id3' 
													end 
										else if @ID4 is null 
														begin
															 set @ID4 = @IdT
															 set @Clmn = @Clmn + ', ' + @IdT + ' Id4'
															 set @JoinClmn = @JoinClmn + ' and T.' + @IdT + ' =  R.Id4'
															 set @ClmnUpText = @ClmnUpText + ' AND ' + @IdT + ' =  @Id4'
															 set @ClmnFetch = @ClmnFetch + ', @Id4' 
														end 

										fetch next

										from IDlist into @IdT 

									end 
									close IDlist 
									deallocate IDlist

									 set @ClmnNtext = @Clmn 
									while @nbID < 4 
										begin
											set @nbID = @nbID + 1
											set @Clmn = @Clmn + ', Null Id' + convert(char(1), @nbID) 
									end 
									
									-- Liste les colonnes
									declare column_list
									 cursor FAST_FORWARD READ_ONLY
										 for
										 select column_name , 
										case when CHARACTER_MAXIMUM_LENGTH > 4000 then 4000
										else CHARACTER_MAXIMUM_LENGTH
										end nblen
										 from information_schema.columns 
										where table_name = @table_name
										 and data_type not in ('image', 'money')
										 and CHARACTER_MAXIMUM_LENGTH is not null 

										open column_list 
											fetch next
											 from column_list into @column_name, @nblen 
												while @@fetch_status=0 
													begin 
													-- Decommentez la ligne suivante pour afficher le nom de toutes les colonnes parcourues
													 -- print @table_name + ' -- ' + @column_name
													
													 set @sqlSelect = N' Select ' + char(39) + @table_name + char(39) + ' table_name, ' + char(39) + @column_name + char(39) + ' column_name, ' + @Clmn + ' , 1 res, convert(nvarchar(' + convert(nvarchar(100), @nblen) + '), [' + @column_name + ']) column_content ,  len(convert(varchar(8000), [' + @column_name + '])) column_lenght from ' + @table_name + ' where [' + @column_name + '] like ' + char(39) + '%' + @condition + '%' + char(39) 
													 -- Decommentez la ligne suivante pour afficher l'ensemble des requêtes SELECT effectuées
													 --print @sqlSelect

													 insert into t_Res 
													 exec sp_executesql @sqlSelect

													 set @nb_enr = 0
													 select @nb_enr= count(*)
													 from t_res
	
													 if @Id1 is not null and @nb_enr > 0 
														begin 
															if not exists(select max(column_lenght)
															   from t_Res
															   where res = 1
															   group by column_lenght 
															   having max(column_lenght) > 4000) 

																begin 

																	set @sqlUpdate = N' update ' + @table_name + ' Set [' + @column_name + '] = replace(convert(nvarchar(' + convert(nvarchar(100), @nblen) + '), [' + @column_name + ']), ' + char(39) + @condition + char(39) + ' , ' + char(39) + @replacestring + char(39) + ') ' + ' From ' + @table_name + ' T inner join t_Res R ' + @JoinClmn 
																	-- Decommentez la ligne suivante pour afficher l'ensemble des requêtes UPDATE effectuées
																	-- print @sqlUpdate
																	-- print @sqlSelect
																	print 'Colonne mise à jour : [' + @column_name + '] de la table [' + @table_name + ']'

																	-- Ici vous pouvez effectuer un traitement particulier sur les données d'une colonne
																	--	if @Id1 = 'ApplicationId' begin 
																	--		...
																	--	end
					
																	begin tran -- Commentez cette ligne pour effectuer les changements

																		exec sp_executesql @sqlUpdate

																	rollback tran -- Commentez cette ligne pour effectuer les changements
																end
													 if exists(select *
															   from t_Res
															   where column_lenght > 4000) 
														begin
															insert into t_ResExclu (table_name, column_name)
															 values (@table_name, @column_name)

															 if exists(select data_type
																   from information_schema.columns
																   where table_name = @table_name
																		and column_name = @column_name
																		and data_type = 'ntext') 
																begin 
																-- Cas NTEXT
																-- On traite les colonnes exclues par la premiere methode uniquement
																-- et jusqu'à 4 clés primaires
																	if CHARINDEX(@condition, @replacestring) > 0 
																		begin 
																			PRINT 'La valeur de remplacement contient la valeur a remplacer. Cela produira un LOOP. 
																				   Veuillez changer la valeur de remplacement 
																				  (ou executez la demarche en deux fois, par exemple, pour transformer 1234 en 123456 :
																					remplacer d abord 1234 en XXXX, puis XXXX en 123456)' 
																		end 
																	else 
																		begin
																			set @sqlUpdate = N'
																				declare @lenOldString INT
																					  , @Id1 nvarchar(100)
																					  , @Id2 nvarchar(100)
																					  , @Id3 nvarchar(100)
																					  ,	@Id4 nvarchar(100)
																					  , @TextPointer BINARY(16)
																					  , @TextIndex INT
																				SET @lenOldString = DATALENGTH(' + char(39) + @condition + char(39) + ')
																				DECLARE irows CURSOR
																				LOCAL FORWARD_ONLY STATIC READ_ONLY FOR
																					SELECT ' + @ClmnNtext + ' 
																					FROM ' + @table_name + '
																					WHERE PATINDEX(' + char(39) + '%' + @condition + '%' + char(39) + ', ' + @column_name + ') > 0
																						OPEN irows
																						FETCH NEXT FROM irows INTO ' + @ClmnFetch + '
																							WHILE (@@FETCH_STATUS = 0)
																							BEGIN
																								SELECT
																									@TextPointer = TEXTPTR(' + @column_name + '),
																									@TextIndex = PATINDEX(' + char(39) + '%' + @condition + '%' + char(39) + ', ' + @column_name +')
																								FROM  ' + @table_name + ' WHERE ' + @ClmnUpText + '
																								WHILE
																								(
																									SELECT
																										PATINDEX(' + char(39) + '%' + @condition + '%' + char(39) + ', ' + @column_name +')
																									FROM  ' + @table_name + ' WHERE ' + @ClmnUpText + '
																								) > 0
																								BEGIN
																									SELECT
																										@TextIndex = PATINDEX(' + char(39) + '%' + @condition + '%' + char(39) + ', ' + @column_name +')-1
																									FROM  ' + @table_name + ' WHERE ' + @ClmnUpText + '

																									UPDATETEXT ' + @table_name + '.' + @column_name + ' @TextPointer @TextIndex @lenOldString ' + char(39) + @replacestring + char(39) + '
																								END
																								FETCH NEXT FROM irows INTO  ' + @ClmnFetch + '
																							END
																				CLOSE irows
																				DEALLOCATE irows' 

																				-- print @sqlUpdate

																				begin tran -- Commentez cette ligne pour effectuer les changements

																					exec sp_executesql @sqlUpdate 

																				rollback tran -- Commentez cette ligne pour effectuer les changements

																			end 
																		end 
																	end 
																end 

												truncate table t_Res 

												fetch next
															 
												from column_list into @column_name , @nblen 
										end 
									close column_list 
									deallocate column_list 
								end 
								else 
								begin 
									print 'Table sans PK : ' + @table_name 
									-- Traitement particulier des tables sans PK ?

								end 
					fetch next
					from table_list into @table_name 
				end 
	close table_list 
deallocate table_list 
--select * from t_Res order by convert(int, column_name) desc
--select max(column_lenght) from t_Res   where res = 1 group by column_lenght having max(column_lenght) > 4000

-- Affiche les colonnes traitées en NTEXT
-- print 'Table traitées en NTEXT : ' 
-- select table_name table_name_ntext, column_name column_name_ntext from t_ResExclu

drop table t_Res
drop table t_ResExclu