SET NOCOUNT ON
DECLARE @Str varchar(50)
,@CName varchar(50)
,@CID varchar(20)
,@XTYPE varchar(50)
,@ReturnError NVARCHAR(4000)
SET @str='</title>'--要搜索的字符串
SET @ReturnError='开始...';
Declare
cur_Depart
Cursor
For select name,id,xtype from syscolumns where id in (Select id From sysobjects Where xtype ='U')
and xtype in (select xtype from systypes where name in ( 'varchar', 'nvarchar', 'char', 'nchar','text','ntext')) order by id
Open cur_Depart
Fetch From cur_Depart into @CName,@CID,@XTYPE
While @@Fetch_Status=0
Begin
Declare @Sql nvarchar(500)
Declare @TName nvarchar(500)
Declare @Count int
SET @TName=(Select name From sysobjects Where ID=@CID)
SET @Sql= ' SELECT @c=Count(1) from '+@TName+' WHERE PATINDEX(''%'+@Str+'%'','+@CName+')>0;';
EXEC sp_executesql @Sql,N'@c int output',@Count output
IF (@Count>0)
BEGIN
EXEC('UPDATE '+@TName+' Set '+@CName+'=SUBSTRING(CAST('+@CName+' as NVarchar(4000)),1,CHARINDEX('''+@Str+''',CAST('+@CName+' as NVarchar(4000)))-1) WHERE LEN(ISNULL(CAST('+@CName+' as NVarchar(4000)),''''))>0 AND CHARINDEX('''+@Str+''',CAST('+@CName+' as NVarchar(4000)))>0;');
SET @ReturnError=@ReturnError+ @TName+'.'+@CName+'清除垃圾;';
END
Fetch From cur_Depart into @CName,@CID,@XTYPE
End
Close cur_Depart
Deallocate cur_Depart
SELECT @ReturnError+'结束'