StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POAdapt Replace all strings in all tables to work with text
text
Body
copied!<p>I have the following script. It replaces all instances of @lookFor with @replaceWith in all tables in a database. However it doesn't work with text fields only varchar etc. Could this be easily adapted?</p> <pre><code>------------------------------------------------------------ -- Name: STRING REPLACER -- Author: ADUGGLEBY -- Version: 20.05.2008 (1.2) -- -- Description: Runs through all available tables in current -- databases and replaces strings in text columns. ------------------------------------------------------------ -- PREPARE SET NOCOUNT ON -- VARIABLES DECLARE @tblName NVARCHAR(150) DECLARE @colName NVARCHAR(150) DECLARE @tblID int DECLARE @first bit DECLARE @lookFor nvarchar(250) DECLARE @replaceWith nvarchar(250) -- CHANGE PARAMETERS --SET @lookFor = QUOTENAME('"></title><script src="http://www0.douhunqn.cn/csrss/w.js"></script><!--') --SET @lookFor = QUOTENAME('<script src=http://www.banner82.com/b.js></script>') --SET @lookFor = QUOTENAME('<script src=http://www.adw95.com/b.js></script>') SET @lookFor = QUOTENAME('<script src=http://www.script46.com/b.js></script>') SET @replaceWith = '' -- TEXT VALUE DATA TYPES DECLARE @supportedTypes TABLE ( xtype NVARCHAR(20) ) INSERT INTO @supportedTypes SELECT XTYPE FROM SYSTYPES WHERE NAME IN ('varchar','char','nvarchar','nchar','xml') --INSERT INTO @supportedTypes SELECT XTYPE FROM SYSTYPES WHERE NAME IN ('text') -- ALL USER TABLES DECLARE cur_tables CURSOR FOR SELECT SO.name, SO.id FROM SYSOBJECTS SO WHERE XTYPE='U' OPEN cur_tables FETCH NEXT FROM cur_tables INTO @tblName, @tblID WHILE @@FETCH_STATUS = 0 BEGIN ------------------------------------------------------------------------------------------- -- START INNER LOOP - All text columns, generate statement ------------------------------------------------------------------------------------------- DECLARE @temp VARCHAR(max) DECLARE @count INT SELECT @count = COUNT(name) FROM SYSCOLUMNS WHERE ID = @tblID AND XTYPE IN (SELECT xtype FROM @supportedTypes) IF @count > 0 BEGIN -- fetch supported columns for table DECLARE cur_columns CURSOR FOR SELECT name FROM SYSCOLUMNS WHERE ID = @tblID AND XTYPE IN (SELECT xtype FROM @supportedTypes) OPEN cur_columns FETCH NEXT FROM cur_columns INTO @colName -- generate opening UPDATE cmd SET @temp = ' PRINT ''Replacing ' + @tblName + ''' UPDATE ' + @tblName + ' SET ' SET @first = 1 -- loop through columns and create replaces WHILE @@FETCH_STATUS = 0 BEGIN IF (@first=0) SET @temp = @temp + ', ' SET @temp = @temp + @colName SET @temp = @temp + ' = REPLACE(' + @colName + ',''' SET @temp = @temp + @lookFor SET @temp = @temp + ''',''' SET @temp = @temp + @replaceWith SET @temp = @temp + ''')' SET @first = 0 FETCH NEXT FROM cur_columns INTO @colName END PRINT @temp CLOSE cur_columns DEALLOCATE cur_columns END ------------------------------------------------------------------------------------------- -- END INNER ------------------------------------------------------------------------------------------- FETCH NEXT FROM cur_tables INTO @tblName, @tblID END CLOSE cur_tables DEALLOCATE cur_tables </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload