Advanced Search

Microsoft SQL Server: Find and replace.

/ Published in: SQL
Save to your folder(s)
Clears supplied text from database tables.
Does not clear the string out of TEXT or NTEXT tables.

Expand | Embed | Plain Text

Copy this code and paste it in your HTML 
  1. DECLARE @SQL NVARCHAR(4000)
  2. DECLARE @InsertedValue NVARCHAR(1000)
  3. SET @InsertedValue = 'The Script tags which were inserted'
  4. DECLARE cur CURSOR FOR
  5.   	SELECT 'update [' + sysusers.name + '].[' + sysobjects.name + ']
  6.   		set [' + syscolumns.name + '] = replace([' + syscolumns.name + '], ''' + @InsertedValue + ''', '''')'
  7.   	FROM syscolumns
  8.   	JOIN sysobjects ON syscolumns.id = sysobjects.id
  9.   		AND sysobjects.xtype = 'U'
  10.   	JOIN sysusers ON sysobjects.uid = sysusers.uid
  11.   	WHERE syscolumns.xtype IN (35, 98, 99, 167, 175, 231, 239, 241, 231)
  12.   OPEN cur
  13.   FETCH NEXT FROM cur INTO @SQL
  14.   WHILE @@FETCH_STATUS = 0
  15.   BEGIN
  16.   	EXEC (@SQL)
  17.   	FETCH NEXT FROM cur INTO @SQL
  18.   END
  19.   CLOSE cur
  20.   DEALLOCATE cur

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.