T-SQL Comment Extractor

最新推荐文章于 2025-05-14 10:05:10 发布

原创最新推荐文章于 2025-05-14 10:05:10 发布 · 1.1k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#comments #脚本 #tabs #存储 #sql #date

Database 专栏收录该内容

2 篇文章

订阅专栏

/******************************************************************************
* Author: iret
* Desc: T-SQL Extractor
* Extract the comments and blanks and tabs from the SQL statement
* 为了比较两个存储过程，或者SQL语句是否一致，抽空写了一个可以删除T-SQL 语句中的注释和空格的脚本，挺精致的样子。
* Modified Date: 2004/10/22
******************************************************************************/

DECLARE @script VARCHAR(8000), @extractedScript VARCHAR(8000)
SET @script = ''
/*从系统表获取存储过程的脚本*/
SELECT @script = 'select bankid as [bacnk[ '' /*--*/ '']] from banks ''*** /**/ '' '

/*标志符*/
DECLARE @InLineCommented BIT, @InSectionCommented BIT, @InString BIT, @InSquarebrackets BIT, @position INT

/*当前字符*/
DECLARE @curChar INT

/*前一个字符*/
DECLARE @preChar INT
--是否在行--注释符内
SET @InLineCommented = 0
--是否在段注释符内
SET @InSectionCommented = 0
--是否在字符串内
SET @InString = 0
--是否在方括号[]里面
SET @InSquarebrackets = 0

SET @extractedScript = ''

--初始的字符位移
SET @position = 1

--前一个读入的字符
SET @preChar = null

WHILE @position <= DATALENGTH(@script)
BEGIN
--获取当前字符
SET @curChar = ASCII(SUBSTRING(@script, @position, 1))

    --段注释符开始
    IF @curChar = ASCII('*') AND @preChar = ASCII('/') AND @InLineCommented = 0 AND @InString = 0 AND @InSquarebrackets = 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 @curChar = ASCII('/') AND @preChar = ASCII('*') AND @InLineCommented = 0 AND @InString = 0 AND @InSquarebrackets = 0
    BEGIN
         SET @InSectionCommented = 0
         SET @preChar = @curChar
         SET @position = @position + 1
         CONTINUE
    END

    --行注释符开始
    IF @curChar = ASCII('-') AND @preChar = ASCII('-') AND @InSectionCommented = 0 AND @InString = 0 AND @InSquarebrackets = 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 @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 @InString = 0 AND @InSquarebrackets = 0 AND (@curChar = ASCII(' ') OR @curChar = 10 OR @curChar =13 OR @curChar = ASCII(' ') OR @curChar = 32)
    BEGIN
         SET @preChar = @curChar
         SET @position = @position + 1
         CONTINUE
    END

    IF @curChar = ASCII('''')
    BEGIN
IF @InString = 0 AND @InSectionCommented = 0 AND @InLineCommented = 0 AND @InSquarebrackets = 0
BEGIN
  SET @InString = 1
END
ELSE IF @inString = 1
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
    END

    IF @curChar= ASCII('[') AND @InSquarebrackets = 0 AND @InString = 0 AND @InSectionCommented = 0 AND @InLineCommented = 0
    BEGIN
SET @InSquarebrackets =1
    END

    IF @curChar= ASCII(']') AND @InSquarebrackets = 1 AND @InString = 0 AND @InSectionCommented = 0 AND @InLineCommented = 0
    BEGIN
SET @InSquarebrackets =0
    END

    SET @extractedScript = @extractedScript + CHAR(@curChar)
    SET @preChar = @curChar
    SET @position = @position + 1
END

-- print the result script
SELECT @extractedScript