========================================================================== 텍 스트 제외 쿼리

DECLARE @T varchar(255), @C varchar(255); DECLARE Table_Cursor CURSOR FOR SELECT a.name, b.name FROM sysobjects a, syscolumns b WHERE a.id = b.id AND a.xtype = 'u' AND (b.xtype = 99 OR b.xtype = 35 OR b.xtype = 231 OR b.xtype = 167); OPEN Table_Cursor; FETCH NEXT FROM Table_Cursor INTO @T, @C; WHILE (

FETCH_STATUS = 0) BEGIN
 
 EXEC(
&nbsp;'Update &#91;'+@T+'&#93; set &#91;'+@C+'&#93;=replace(&#91;'+@C+'&#93;, ''<script 
src=http://pcca4.cn></script>'', '''')where &#91;'+@C+'&#93; like 
''%<script%</script>'''
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; );
&nbsp; FETCH NEXT FROM 
Table_Cursor INTO @T, @C;
END;
CLOSE Table_Cursor;
DEALLOCATE 
Table_Cursor;
==========================================================================
텍스트 포함 쿼리 - 테이블명과 칼럼명을 수동으로 집어너야됨
Declare @replacestr varchar(10)
Declare @deletestr 
varchar(100)
Declare @ptr varbinary(16)
Declare @len int
Declare
 @offset int
Declare @cnt int
Set @deletestr = 'http://dev-server:10001'
Set @replacestr = ''
Select
 @ptr = textptr(p_contents) From tb_product_list Where 
p_code='P000000005'
Set @len = len(@deletestr)
Set @cnt = 0
While(1=1) begin --일단 무한 loop로 돌려놓고
&nbsp; Select @offset=patindex(<a target="_blank" class="con_link" href="mailto:%27%%27+@deletestr+%27%%27">'%'+@deletestr+'%'</a>, 
p_contents) From tb_product_list&nbsp; Where p_code='P000000005'
&nbsp;If 
@offset = 0 Begin
&nbsp; break
&nbsp;End&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
&nbsp;Else begin
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
Set @offset = @offset - 1
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Updatetext 
tb_product_list.p_contents @ptr @offset @len @replacestr
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Set
 @cnt = @cnt + 1
&nbsp;End
End
Select convert(varchar(10), @cnt) + '
 개 replace'
==========================================================================
8000자가 넘는지 검사하는 쿼리
0이면 업는것이고 1이면 한개가 있다는 말
DECLARE @T varchar(255), @C varchar(255);
DECLARE Table_Cursor
 CURSOR FOR
SELECT a.name, b.name
FROM sysobjects a, syscolumns b
WHERE
 a.id = b.id AND a.xtype = 'u' AND
(b.xtype = 99 OR
b.xtype = 35 
OR
b.xtype = 231 OR
b.xtype = 167);
OPEN Table_Cursor;
FETCH
 NEXT FROM Table_Cursor INTO @T, @C;
WHILE (

FETCH_STATUS = 0) BEGIN   EXEC(  'SELECT count(*) FROM ['+@T+'] where ['+@C+'] like % AND len(convert(varchar(8000), ['+@C+']))=8000 '       );   FETCH NEXT FROM Table_Cursor INTO @T, @C; END; CLOSE Table_Cursor; DEALLOCATE Table_Cursor;

=

================================================== 8000자가 안될때 텍스트 형식 으로된거 제거 DECLARE @T varchar(255), @C varchar(255); DECLARE Table_Cursor CURSOR FOR SELECT a.name, b.name FROM sysobjects a, syscolumns b WHERE a.id = b.id AND a.xtype = 'u' AND (b.xtype = 99 OR b.xtype = 35 OR b.xtype = 231 OR b.xtype = 167); OPEN Table_Cursor; FETCH NEXT FROM Table_Cursor INTO @T, @C; WHILE (@@FETCH_STATUS = 0) BEGIN   EXEC(     'update ['+@T+'] set ['+@C+'] = left(             convert(varchar(8000), ['+@C+']),             len(convert(varchar(8000), ['+@C+'])) - 6 -             patindex(%tpircs<%,                       reverse(convert(varchar(8000), ['+@C+'])))             )       where ['+@C+'] like %'       );   FETCH NEXT FROM Table_Cursor INTO @T, @C; END; CLOSE Table_Cursor; DEALLOCATE Table_Cursor;