Posted By

jprochazka on 01/11/11


Tagged

mssql


Versions (?)

Microsoft SQL Server: Find and replace.


 / Published in: SQL
 

Clears supplied text from database tables. Does not clear the string out of TEXT or NTEXT tables.

  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  

You need to login to post a comment.