StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Yeah. What I ended up doing is I converted to varchar(max) on the fly, and the replace took care of the rest.</p> <pre><code> -- 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 = ('bla') 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','ntext','text') --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 PRINT 'UPDATE ' + @tblName + ' SET' SET @first = 1 -- loop through columns and create replaces WHILE @@FETCH_STATUS = 0 BEGIN IF (@first=0) PRINT ',' PRINT @colName + ' = REPLACE(convert(nvarchar(max),' + @colName + '),''' + @lookFor + ''',''' + @replaceWith + ''')' SET @first = 0 FETCH NEXT FROM cur_columns INTO @colName END PRINT 'GO' 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