T-SQLExtractor

来源：互联网发布：罗伯特唐尼知乎编辑：程序博客网时间：2024/05/09 00:14

/******************************************************************************
*Author: iret
*Desc:T-SQL Extractor
*         ExtractthecommentsandblanksandtabsfromtheSQLstatement
*为了比较两个存储过程，或者SQL语句是否一致，抽空写了一个可以删除T-SQL语句中的注释和空格的脚本，挺精致的样子。
*CreatedDate:2004/10/21
******************************************************************************/

DECLARE@scriptVARCHAR(8000),@extractedScriptVARCHAR(4000)
SET@script=''
/*从系统表获取存储过程的脚本*/
SELECT@script=@script+[text]
FROMsyscomments,sysobjects
WHERE
   syscomments.[id]=sysobjects.[id]ANDsysobjects.[name]LIKE'%campa_AppSegment'

/*标志符*/
DECLARE@InLineCommentedBIT,@InSectionCommentedBIT,@InStringBIT,@positionINT

/*当前字符*/
DECLARE@curCharINT

/*前一个字符*/
DECLARE@preCharINT

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=0AND@InString=0
   BEGIN
       --SETthesigninsectioncomment
       SET@InSectionCommented=1

         --popthe/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=0AND@InString=0
   BEGIN
         SET@InSectionCommented=0
         SET@preChar=@curChar
         SET@position=@position+1
         CONTINUE
   END

   IF@preChar=ASCII('-')AND@curChar=ASCII('-')AND@InSectionCommented=0AND@InString=0
   BEGIN
         SET@InLineCommented=1

         --popthe/char
         SET@extractedScript=substring(@extractedScript,1,len(@extractedScript)-1)

         SET@preChar=@curChar
         SET@position=@position+1
         CONTINUE1<script type="text/javascript"></script><script type="text/javascript"src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>