|
/******************************************************************************
* 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
| 
注：本站部分文章源于互联网，版权归原作者所有！如有侵权，请原作者与本站联系，本站将立即删除！ 本站文章除特别注明外均可转载，但需注明出处！ [MinTao学以致用网] 