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.


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.