IDC资讯中心
/******************************************************************************
* author: iret
* desc: t-sql extractor
* extract the comments and blanks and tabs from the sql statement
* 为了比较两个存储过程，或者sql语句是否一致，抽空写了一个可以删除t-sql 语句中的注释和空格的脚本，挺精致的样子。
* created date: 2004/10/21
******************************************************************************/
declare @script varchar(8000), @extractedscript varchar(4000)
set @script =
/*从系统表获取存储过程的脚本*/
select @script = @script + [text]
from syscomments, sysobjects
where
syscomments.[id] = sysobjects.[id] and sysobjects.[name] like %campa_appsegment
/*标志符*/
declare @inlinecommented bit, @insectioncommented bit, @instring bit, @position int
/*当前字符*/
declare @curchar int
/*前一个字符*/
declare @prechar int
set @inlinecommented = 0
set @insectioncommented = 0
set @instring = 0
set @extractedscript =
set @position = 1
set @prechar = null
while @position <= datalength(@script)
begin
–获取当前字符
set @curchar = ascii(substring(@script, @position, 1))
if @prechar = ascii(/) and @curchar = ascii(*) and @inlinecommented = 0 and @instring = 0
begin
— set the sign in section comment
set @insectioncommented = 1
–pop the / char
set @extractedscript = substring(@extractedscript,1,len(@extractedscript)-1)
set @prechar = @curchar
set @position = @position + 1
continue
end
if @prechar = ascii(*) and @curchar = ascii(/) and @inlinecommented = 0 and @instring = 0
begin
set @insectioncommented = 0
set @prechar = @curchar
set @position = @position + 1
continue
end
if @prechar = ascii(-) and @curchar = ascii(-) and @insectioncommented = 0 and @instring = 0
begin
set @inlinecommented = 1
–pop the / char
set @extractedscript = substring(@extractedscript,1,len(@extractedscript)-1)
set @prechar = @curchar
set @position = @position + 1
continue
end
if @curchar = ascii() and @instring = 0 and @insectioncommented = 0 and @inlinecommented = 0
begin
set @instring = 1
end
if @instring = 1 and @curchar = ascii()
begin
if ascii(substring(@script, @position+1, 1))= ascii()
begin
set @extractedscript = @extractedscript +
set @position = @position + 1
end
else
begin
set @instring = 0
end
end
if @insectioncommented = 1
begin
set @prechar = @curchar
set @position = @position + 1
continue
end
if @inlinecommented = 1
begin
— if meets the end of the line set the inlinecommented to false
if @curchar = 10 and @prechar = 13
begin
set @inlinecommented = 0
end
set @prechar = @curchar
set @position = @position + 1
continue
end
if @curchar = ascii( ) or @curchar = 10 or @curchar =13 or @curchar = ascii( ) or @curchar = 32
begin
set @prechar = @curchar
set @position = @position + 1
continue
end
set @extractedscript = @extractedscript + char(@curchar)
set @prechar = @curchar
set @position = @position + 1
end
— print the result script
select @extractedscript
