T-SQL Extractor

王朝mssql·作者佚名  2006-01-09
窄屏简体版  字體: |||超大  

/******************************************************************************

* 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

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
 
 
© 2005- 王朝網路 版權所有 導航