/ ************************************************** *************************************** * Author: IRET * DESC: T-SQL Extractor * Extract The Comments and Blanks and Tabs from THE SQL Statement * In order to compare two stored procedures, or if the SQL statement is consistent, the script that can remove annotations and spaces in the T-SQL statement, quite exquisite look. * Modified Date: 2004/10/22 ************************************************** ********************************************* /
Declare @script varchar (8000), @extractedScript varchar (8000) set @script = '' / * Gets the script of stored procedures from the system table * / select @script = 'SELECT BANKID AS [Bacnk [' '/ * - * / '']] From banns '*** / ** /' ''
/ * Marking * / declare @INLINECOMMENTED BIT, @Insectioncomment bit, @instring bit, @insquarebrackets bit, @Position Int
/ * Current character * / declare @curchar int
/ * Previous character * / declare @PRECHAR INT - Whether is in the line - Note @inlinecomment = 0 - Whether to set @insectioncommented = 0 in the paragraph comment in the character string @instring = 0-- Whether in square brackets [] SET @insquarebrackets = 0
Set @extractedscript = ''
- Initial character displacement set @Position = 1
- Previous read character set @PRECHAR = NULL
While @Position <= DATALENGTH (@script) Begin - Get the current character set @curchar = ascii (@script, @Position, 1))
- Segment commentary start if @curchar = ascii ('*') and @prechar = ascii ('/') and @INLINECOMMENTED = 0 and @instring = 0 and @insquarebrackets = 0 Begin - set the sign in section intermet Set @insectioncommented = 1
--POP the / char set @extractedscript = substring (@ extractedscript, 1, len (@extractedscript) -1)
Set @PRECHAR = @curchar set @Position = @Position 1 Continue End - Segment comment end if @curchar = ascii ('/') and @prechar = ascii ('*') and @inlinecomment = 0 and @instring = 0 and @insquarebrackets = 0 begin set @insectioncommented = 0 set @prechar = @curchar set @Position = @Position 1 Continue End
- Total note start IF @curchar = ascii ('-') and @PRECHAR = ASCII ('-') and @Sectioncommented = 0 and @instring = 0 and @insquarebrackets = 0 begin set @inlinecomment = 1
--POP the / char set @extractedscript = substring (@ extractedscript, 1, len (@extractedscript) -1)
Set @PRECHAR = @curchar set @Position = @Position 1 Continue end if @Sectioncommented = 1 begin set @prechar = @curchar set @Position = @Position 1 Continue End
- Total beta 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 @inlinecomment = 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 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 ENDIF @ 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 1END
- Print The Result ScriptSelect @extractedScript
