Back to OIM Explorer

dbo.QBM_FCVStringToListSQLMo_int

Table FunctionSQL_TABLE_VALUED_FUNCTIONSandbox DB

Table Function.

Source: sandbox-db sys.sql_modules

Source size: 17.464 characters

Interpretation

  • Database function. Usually supports views, validation, or calculated predicates; look at referenced-by entries for callers.

Relations

  • No extracted relations.

Typed Edges

  • references source dbo.QBM_FCVStringToList source text reference
  • references source dbo.QBM_FTSQLKeywords source text reference

References

Referenced By

Complete Source

SQL734 lines
1CREATE FUNCTION dbo.QBM_FCVStringToListSQLMo_int(2  @SQLIn nvarchar(max),3  @CurrentLevel int,4  @Check int,5  @withLiteralTopDown BIT6) RETURNS @erg TABLE(SortOrder int,7MorphemType nvarchar(16) collate Database_default,8MorphemSubType nvarchar(16) collate Database_default,9isConflicted BIT DEFAULT 0,10CurrentLevel int DEFAULT 0,11Morphem nvarchar(max) collate Database_default,12p1 nvarchar(256) collate Database_default,13p2 nvarchar(256) collate Database_default,14LenMorphem int NOT NULL,15TargetLine int NOT NULL primary key(SortOrder)16)17AS18BEGIN19  DECLARE @Element nvarchar(max)20  DECLARE @i int21  DECLARE @len int22  DECLARE @Zustand int23  DECLARE @Nesting int24  DECLARE @NextChar nvarchar(1)25  DECLARE @Next2Char nvarchar(2)26  DECLARE @MaxlenAlias int = 1027  DECLARE @SourceCode nvarchar(max)28  SELECT29    @SourceCode = CONCAT(@sqlIn,30    nchar(13),31    nchar(10))32  SELECT @Element = N ''33  SELECT @len = len(@SourceCode)34  DECLARE @TargetLine int = 035  DECLARE @LineLength int = 036  DECLARE @LineLengthMax int = 15037  DECLARE @result QBM_YMorphemInformation38  DECLARE @TwoCharMorphem QBM_YSingleGUID39  INSERT INTO @TwoCharMorphem(UID_SingleGuid)40  VALUES('+='),41  ('-='),42  ('<>'),43  ('!='),44  ('<='),45  ('>=')46  DECLARE @OneCharMorphem QBM_YSingleGUID47  INSERT INTO @OneCharMorphem(UID_SingleGuid)48  VALUES('('),49  (')'),50  ('='),51  (','),52  (';'),53  ('+'),54  ('-'),55  ('*'),56  ('/'),57  ('%'),58  ('<'),59  ('>'),60  (':'),61  ('^'),62  ('!'),63  ('~'),64  ('&'),65  ('|')66  SELECT @Zustand = 067  SELECT @i = 168  WHILE @i <= @len69  BEGIN70    SELECT71      @Next2Char = substring(@SourceCode,72      @i,73      2)74    IF len(@Next2Char) < 275    BEGIN76      SELECT @Next2Char =77      LEFT(CONCAT(@Next2Char, nchar(13), nchar(10)),78      2)79    END80    SELECT @NextChar =81    LEFT(@Next2Char,82    1)83    IF @Zustand = 084    BEGIN85      IF @Next2Char = N '--'86      BEGIN87        SELECT @Zustand = 188        SELECT @i += 2 CONTINUE89      END90      IF @Next2Char = N '/*'91      BEGIN92        SELECT @Zustand = 293        SELECT @Nesting = 194        SELECT @i += 2 CONTINUE95      END96      IF @Next2Char = N 'N'''97      BEGIN98        SELECT @Zustand = 399        SELECT @Element += @Next2Char100        SELECT @i += 2 CONTINUE101      END102      IF @NextChar = N ''''103      BEGIN104        SELECT @Zustand = 3105        SELECT @Element += @NextChar106        SELECT @i += 1 CONTINUE107      END108      IF @next2char IN(109        SELECT UID_SingleGuid110        FROM @TwoCharMorphem)111      BEGIN112        INSERT INTO @result(Morphemtype,113        MorphemSubType,114        morphem,115        CurrentLevel,116        LenMorphem,117        TargetLine)118        VALUES('Morphem',119        '2 Chars',120        @next2char,121        @CurrentLevel,122        len(@Next2Char),123        @TargetLine)124        SELECT @LineLength += len(@Next2Char) +1125        IF @LineLength > @LineLengthMax126        BEGIN127          SELECT @LineLength = 0128          SELECT @TargetLine += 1129        END130        SELECT @i += 2131        SELECT @Zustand = 0132        SELECT @element = N '' CONTINUE133      END134      IF @nextChar IN(135        SELECT UID_SingleGuid136        FROM @OneCharMorphem)137      BEGIN138        INSERT INTO @result(Morphemtype,139        MorphemSubType,140        morphem,141        CurrentLevel,142        LenMorphem,143        TargetLine)144        VALUES('Morphem',145        '1 Char',146        @NextChar,147        @CurrentLevel,148        len(@NextChar),149        @TargetLine)150        SELECT @LineLength += len(@NextChar) +1151        IF @LineLength > @LineLengthMax152        BEGIN153          SELECT @LineLength = 0154          SELECT @TargetLine += 1155        END156        SELECT @i += 1157        SELECT @Zustand = 0158        SELECT @element = N '' CONTINUE159      END160      IF @NextChar IN(N ' ',161      nchar(9))162      BEGIN163        SELECT @Zustand = 4164        SELECT @i += 1 CONTINUE165      END166      IF @NextChar = nchar(13)167      BEGIN168        SELECT @i += 1 CONTINUE169      END170      IF @NextChar = nchar(10)171      BEGIN172        SELECT @i += 1 CONTINUE173      END174      SELECT @Zustand = 5175      SELECT @i += 1176      SELECT @Element += @NextChar CONTINUE177    END178    IF @Zustand = 1179    BEGIN180      IF @nextchar IN(nchar(13),181      nchar(10))182      BEGIN183        SELECT @Zustand = 0 CONTINUE184      END185      SELECT @i += 1 CONTINUE186    END187    IF @Zustand = 2188    BEGIN189      IF @next2char = N '/*'190      BEGIN191        SELECT @Nesting += 1192        SELECT @i = @i+2 CONTINUE193      END194      IF @next2char = N '*/'195      BEGIN196        SELECT @Nesting -= 1197        IF @nesting = 0198        BEGIN199          SELECT @Zustand = 0200        END201        SELECT @i = @i+2 CONTINUE202      END203      SELECT @i += 1 CONTINUE204    END205    IF @Zustand = 3206    BEGIN207      IF @Next2Char = N ''''''208      BEGIN209        SELECT @Element += @next2char210        SELECT @i += 2 CONTINUE211      END212      IF @nextchar = N ''''213      BEGIN214        SELECT @Zustand = 0215        SELECT @i += 1216        SELECT @Element += @nextchar217        INSERT INTO @result(Morphemtype,218        MorphemSubType,219        morphem,220        CurrentLevel,221        LenMorphem,222        TargetLine)223        VALUES('Literal',224        'String',225        @Element,226        @CurrentLevel,227        len(@Element),228        @TargetLine)229        SELECT @LineLength += len(@Element) +1230        IF @LineLength > @LineLengthMax231        BEGIN232          SELECT @LineLength = 0233          SELECT @TargetLine += 1234        END235        IF @withLiteralTopDown = 1236        BEGIN237          IF238          LEFT(@element,239          2) = 'N'''240          BEGIN241            SELECT242              @element = substring(@element,243              3,244              len(@element) - 3)245          END246          ELSE247          BEGIN248            SELECT249              @element = substring(@element,250              2,251              len(@element) - 2)252          END253          SELECT254            @element = replace(@element,255            '''''',256            '''')257          INSERT INTO @result(Morphemtype,258          MorphemSubType,259          morphem,260          CurrentLevel,261          LenMorphem,262          TargetLine)263          SELECT264            x.Morphemtype,265            x.MorphemSubType,266            x.morphem,267            x.CurrentLevel,268            len(x.morphem),269            @TargetLine270          FROM dbo.QBM_FCVStringToListSQLMo_int(@element,271          @CurrentLevel + 1,272          @Check,273          @withLiteralTopDown) x274        END275        SELECT @element = N '' CONTINUE276      END277      SELECT @i += 1278      SELECT @Element += @nextchar CONTINUE279    END280    IF @Zustand = 4281    BEGIN282      IF @nextchar IN(N ' ',283      nchar(9),284      nchar(13),285      nchar(10))286      BEGIN287        SELECT @i += 1 CONTINUE288      END289      SELECT @zustand = 0 CONTINUE290    END291    IF @zustand = 5292    BEGIN293      IF @next2Char IN(N '--',294      N '/*') OR @nextchar IN(N '''',295      nchar(10),296      nchar(13),297      N ' ',298      nchar(9)) OR @next2char IN(299      SELECT UID_SingleGuid300      FROM @TwoCharMorphem) OR @nextChar IN(301      SELECT UID_SingleGuid302      FROM @OneCharMorphem)303      BEGIN304        SELECT @Zustand = 0305        INSERT INTO @result(Morphemtype,306        MorphemSubType,307        morphem,308        CurrentLevel,309        LenMorphem,310        TargetLine)311        VALUES('Morphem',312        NULL,313        @Element,314        @CurrentLevel,315        len(@element),316        @TargetLine)317        SELECT @LineLength += len(@Element) +1318        IF @LineLength > @LineLengthMax319        BEGIN320          SELECT @LineLength = 0321          SELECT @TargetLine += 1322        END323        SELECT @element = N '' CONTINUE324      END325      SELECT @i += 1326      SELECT @Element += @nextchar CONTINUE327    END328  END329  IF @Check = -1330  BEGIN331    GOTO EndLabel332  END333  UPDATE @result334  SET p1 = substring(Morphem,335  1,336  PATINDEX(N '%..%', Morphem) -1),337  p2 = substring(Morphem,338  PATINDEX(N '%..%', Morphem) +2,339  256)340  WHERE341    morphemtype = 'Morphem' AND morphemsubtype IS NULL AND PATINDEX(N '%..%',342  Morphem) > 0 AND CurrentLevel = @CurrentLevel343  UPDATE @result344  SET p1 = substring(Morphem,345  1,346  PATINDEX(N '%.%', Morphem) -1),347  p2 = substring(Morphem,348  PATINDEX(N '%.%', Morphem) +1,349  256)350  WHERE351    morphemtype = 'Morphem' AND morphemsubtype IS NULL AND p1 IS NULL AND PATINDEX(N '%.%',352  Morphem) > 0 AND CurrentLevel = @CurrentLevel353  UPDATE @result354  SET MorphemType = 'Literal',355  MorphemSubType = 'Float',356  p1 = NULL,357  p2 = NULL358  WHERE359    morphemtype = 'Morphem' AND morphemSubtype IS NULL AND isnumeric(p1) = 1 AND isnumeric(p2) = 1 AND CurrentLevel = @CurrentLevel360  UPDATE @result361  SET morphemType = 'Literal',362  MorphemSubtype = 'Int'363  WHERE364    morphemtype = 'Morphem' AND isnumeric(morphem) = 1 AND morphemSubtype IS NULL AND CurrentLevel = @CurrentLevel365  UPDATE @result366  SET morphemType = 'Literal',367  MorphemSubtype = 'binary'368  WHERE369    morphemtype = 'Morphem' AND morphem LIKE '0' + 'x%' AND morphemSubtype IS NULL AND CurrentLevel = @CurrentLevel370  UPDATE @result371  SET morphemType = 'Variable',372  MorphemSubtype = ''373  WHERE374    morphemtype = 'Morphem' AND375  LEFT(Morphem,376  1) = '@' AND substring(Morphem,377  2,378  1) <> '@' AND CurrentLevel = @CurrentLevel379  IF @Check = 0380  BEGIN381    GOTO endLabel382  END383  UPDATE @result384  SET Morphemtype = 'KeyWord',385  MorphemSubtype = x.KeywordArea386  FROM @Result r387  JOIN dbo.QBM_FTSQLKeywords(1) x388    ON r.MorphemType = 'Morphem' AND r.MorphemSubtype IS NULL AND r.Morphem = x.Keyword AND x.KeywordArea = 'T-SQL'389  WHERE390    r.CurrentLevel = @CurrentLevel391  UPDATE @result392  SET Morphemtype = CASE393  WHEN x.system_type_id = x.user_type_id THEN394  'SystemObject'395  ELSE 'SchemaObject'396  END,397  MorphemSubtype = 'Type'398  FROM @Result r399  JOIN sys.types x400    ON r.MorphemType = 'Morphem' AND r.MorphemSubtype IS NULL AND(r.Morphem = x.name OR r.p2 = x.name)401  WHERE402    r.CurrentLevel = @CurrentLevel403  IF @Check = 2404  BEGIN405    GOTO EndLabel406  END407  UPDATE @result408  SET Morphemtype = 'SchemaObject',409  MorphemSubtype = 'Table'410  FROM @Result r411  JOIN sys.tables x412    WITH(readpast)413    ON r.MorphemType = 'Morphem' AND r.MorphemSubtype IS NULL AND(r.Morphem = x.name OR r.p2 = x.name)414  WHERE415    r.CurrentLevel = @CurrentLevel416  UPDATE @result417  SET Morphemtype = 'SchemaObject',418  MorphemSubtype = 'Table'419  FROM @Result r420  WHERE421    r.MorphemType = 'Morphem' AND r.MorphemSubtype IS NULL AND(r.Morphem LIKE '#%' OR r.p2 LIKE '#%') AND r.CurrentLevel = @CurrentLevel422  UPDATE @result423  SET Morphemtype = 'SchemaObject',424  MorphemSubtype = 'View'425  FROM @Result r426  JOIN sys.views x427    WITH(readpast)428    ON r.MorphemType = 'Morphem' AND r.MorphemSubtype IS NULL AND(r.Morphem = x.name OR r.p2 = x.name)429  WHERE430    r.CurrentLevel = @CurrentLevel431  UPDATE @result432  SET Morphemtype = 'SchemaObject',433  MorphemSubtype = 'TableFunction'434  FROM @Result r435  JOIN sys.objects x436    WITH(readpast)437    ON r.MorphemType = 'Morphem' AND r.MorphemSubtype IS NULL AND(r.Morphem = x.name OR r.p2 = x.name)438  WHERE439    x.type IN('IF',440  'TF') AND r.CurrentLevel = @CurrentLevel441  UPDATE @result442  SET Morphemtype = 'SchemaObject',443  MorphemSubtype = 'Function'444  FROM @Result r445  JOIN sys.objects x446    WITH(readpast)447    ON r.MorphemType = 'Morphem' AND r.MorphemSubtype IS NULL AND(r.Morphem = x.name OR r.p2 = x.name)448  WHERE449    x.type IN('FN') AND r.CurrentLevel = @CurrentLevel450  UPDATE @result451  SET Morphemtype = 'SchemaObject',452  MorphemSubtype = 'Procedure'453  FROM @Result r454  JOIN sys.objects x455    WITH(readpast)456    ON r.MorphemType = 'Morphem' AND r.MorphemSubtype IS NULL AND(r.Morphem = x.name OR r.p2 = x.name)457  WHERE458    x.type IN('P') AND r.CurrentLevel = @CurrentLevel459  UPDATE @result460  SET morphemtype = 'SystemObject',461  MorphemSubType = 'Table'462  WHERE463    p1 IN(464  SELECT name465  FROM sys.schemas466    WITH(readpast)) AND p1 <> 'dbo' AND p2 > ' ' AND CurrentLevel = @CurrentLevel AND p2 NOT LIKE 'sp[_]%' AND morphemtype = 'Morphem' AND morphemsubtype467  IS NULL468  UPDATE @result469  SET morphemtype = 'SystemObject',470  MorphemSubType = 'Table'471  WHERE472    EXISTS(473  SELECT TOP 1 1474  FROM sys.databases sd475  WHERE476    p1 = sd.name collate database_default) AND p2 > ' ' AND CurrentLevel = @CurrentLevel AND p2 NOT LIKE 'sp[_]%' AND morphemtype = 'Morphem' AND477  morphemsubtype IS NULL478  UPDATE @result479  SET MorphemSubtype = 'Table'480  WHERE481    MorphemType = 'Variable' AND morphem IN(482  SELECT r1.Morphem483  FROM @Result r1484  JOIN @Result r2485    ON r1.SortOrder + 1 = r2.SortOrder AND r1.Morphemtype = 'Variable' AND r2.Morphem = 'Table'486  JOIN @Result r3487    ON r2.SortOrder + 1 = r3.SortOrder AND r3.Morphem = '(') AND CurrentLevel = @CurrentLevel488  UPDATE @result489  SET Morphemtype = 'ProgramElement',490  MorphemSubtype = 'Label'491  FROM @Result r492  JOIN(493  SELECT *494  FROM @result495  WHERE496    CurrentLevel = @CurrentLevel) AS r2497    ON r.SortOrder +1 = r2.SortOrder498  WHERE499    r.MorphemType = 'Morphem' AND r.MorphemSubtype IS NULL AND r2.Morphem = ':' AND r.CurrentLevel = @CurrentLevel500  UPDATE @result501  SET Morphemtype = 'ProgramElement',502  MorphemSubtype = 'Label'503  FROM @Result r2504  JOIN(505  SELECT *506  FROM @result507  WHERE508    CurrentLevel = @CurrentLevel) AS r1509    ON r1.SortOrder +1 = r2.SortOrder510  WHERE511    r2.MorphemType = 'Morphem' AND r2.MorphemSubtype IS NULL AND r1.Morphem = 'Goto' AND r2.CurrentLevel = @CurrentLevel512  UPDATE @result513  SET Morphemtype = r1.Morphemtype,514  MorphemSubtype = 'TableAlias1'515  FROM @Result r2516  JOIN(517  SELECT *518  FROM @result519  WHERE520    CurrentLevel = @CurrentLevel) AS r1521    ON r1.morphemSubtype IN('Table',522  'view',523  'TableFunction') AND r1.SortOrder + 1 = r2.SortOrder524  WHERE525    r2.MorphemType = 'Morphem' AND r2.MorphemSubtype IS NULL AND len(r2.morphem) <= @MaxlenAlias AND r2.p1 IS NULL AND r2.CurrentLevel = @CurrentLevel526  UPDATE @result527  SET Morphemtype = r1.Morphemtype,528  MorphemSubtype = 'TableAlias2'529  FROM @Result r3530  JOIN(531  SELECT *532  FROM @result533  WHERE534    CurrentLevel = @CurrentLevel) AS r1535    ON r1.morphemSubtype IN('Table',536  'view',537  'TableFunction')538  JOIN(539  SELECT *540  FROM @result541  WHERE542    CurrentLevel = @CurrentLevel) AS r2543    ON r2.Morphem = 'AS' AND r1.SortOrder + 1 = r2.SortOrder AND r2.SortOrder +1 = r3.SortOrder544  WHERE545    r3.MorphemType = 'Morphem' AND r3.MorphemSubtype IS NULL AND len(r3.morphem) <= @MaxlenAlias AND r3.p1 IS NULL AND r3.CurrentLevel = @CurrentLevel546  UPDATE @result547  SET Morphemtype = r1.Morphemtype,548  MorphemSubtype = 'TableAlias3'549  FROM @Result r2550  JOIN(551  SELECT *552  FROM @result553  WHERE554    CurrentLevel = @CurrentLevel) r1555    ON r1.morphem = ')' AND r1.SortOrder + 1 = r2.SortOrder556  WHERE557    r2.MorphemType = 'Morphem' AND r2.MorphemSubtype IS NULL AND len(r2.morphem) <= @MaxlenAlias AND r2.p1 IS NULL AND r2.CurrentLevel = @CurrentLevel AND558  r2.morphem <> 'returns'559  UPDATE @result560  SET Morphemtype = r1.Morphemtype,561  MorphemSubtype = 'TableAlias4'562  FROM @Result r3563  JOIN(564  SELECT *565  FROM @result566  WHERE567    CurrentLevel = @CurrentLevel) AS r1568    ON r1.morphem = ')'569  JOIN(570  SELECT *571  FROM @result572  WHERE573    CurrentLevel = @CurrentLevel) AS r2574    ON r2.Morphem = 'AS' AND r1.SortOrder + 1 = r2.SortOrder AND r2.SortOrder +1 = r3.SortOrder575  WHERE576    r3.MorphemType = 'Morphem' AND r3.MorphemSubtype IS NULL AND len(r3.morphem) <= @MaxlenAlias AND r3.p1 IS NULL AND r3.CurrentLevel = @CurrentLevel577  UPDATE @Result578  SET Morphemtype = b.Morphemtype,579  MorphemSubtype = 'TableAlias5'580  FROM @Result a581  JOIN(582  SELECT *583  FROM @result584  WHERE585    CurrentLevel = @CurrentLevel) AS b586    ON b.MorphemSubtype LIKE 'TableAlias_' AND b.Morphem = a.Morphem587  WHERE588    a.MorphemType = 'Morphem' AND a.MorphemSubtype IS NULL AND NOT EXISTS(589  SELECT TOP 1 1590  FROM @Result c591  WHERE592    c.Morphem = a.Morphem AND(c.MorphemSubtype NOT LIKE 'TableAlias_')) AND a.CurrentLevel = @CurrentLevel593  UPDATE @result594  SET Morphemtype = te.Morphemtype,595  MorphemSubtype = 'Column'596  FROM @Result r597  JOIN sys.columns x598    WITH(readpast)599    ON r.MorphemType = 'Morphem' AND r.MorphemSubtype IS NULL AND(r.Morphem = x.name OR r.p2 = x.name)600  JOIN sys.objects t601    WITH(readpast)602    ON x.Object_id = t.Object_id603  JOIN(604  SELECT *605  FROM @result606  WHERE607    CurrentLevel = @CurrentLevel) AS te608    ON te.MorphemSubtype IN('table',609  'View',610  'TableFunction') AND(te.morphem = t.name OR te.p2 = t.name)611  WHERE612    r.CurrentLevel = @CurrentLevel613  UPDATE @result614  SET Morphemtype = ta.Morphemtype,615  MorphemSubtype = 'Column'616  FROM @Result r617  JOIN(618  SELECT *619  FROM @result620  WHERE621    CurrentLevel = @CurrentLevel) AS ta622    ON ta.MorphemSubtype LIKE('tableAlias_') AND r.p1 = ta.morphem623  WHERE624    r.CurrentLevel = @CurrentLevel625  UPDATE @Result626  SET Morphemtype = r1.Morphemtype,627  MorphemSubtype = 'ColumnAlias'628  FROM @Result r3629  JOIN(630  SELECT *631  FROM @result632  WHERE633    CurrentLevel = @CurrentLevel) AS r2634    ON r2.SortOrder +1 = r3.SortOrder635  JOIN(636  SELECT *637  FROM @result638  WHERE639    CurrentLevel = @CurrentLevel) AS r1640    ON r1.SortOrder +1 = r2.SortOrder641  WHERE642    r1.MorphemSubtype = 'Column' AND r2.Morphem = 'as' AND r3.MorphemType = 'Morphem' AND r3.MorphemSubtype IS NULL AND r3.CurrentLevel = @CurrentLevel643  UPDATE @Result644  SET Morphemtype = r1.Morphemtype,645  MorphemSubtype = 'ColumnAlias'646  FROM @Result r2647  JOIN(648  SELECT *649  FROM @result650  WHERE651    CurrentLevel = @CurrentLevel) AS r1652    ON r1.SortOrder +1 = r1.SortOrder653  WHERE654    r1.MorphemSubtype = 'Column' AND r2.MorphemType = 'Morphem' AND r2.MorphemSubtype IS NULL AND r2.CurrentLevel = @CurrentLevel655  UPDATE @result656  SET Morphemtype = 'ProgramElement',657  MorphemSubtype = 'Cur' + 'sor'658  FROM @Result r659  WHERE660    r.MorphemType = 'Morphem' AND r.MorphemSubtype IS NULL AND EXISTS(661  SELECT TOP 1 1662  FROM @Result r1663  JOIN @result r2664    ON r1.SortOrder +1 = r2.SortOrder AND r1.CurrentLevel = @CurrentLevel AND r2.CurrentLevel = @CurrentLevel665  WHERE666    r2.Morphem = r.Morphem AND r2.MorphemType = 'Morphem' AND r2.MorphemSubType IS NULL AND r1.MorphemType = 'Keyword' AND r1.Morphem = 'DECLARE'667  ) AND EXISTS(668  SELECT TOP 1 1669  FROM @Result r1670  JOIN @result r2671    ON r1.SortOrder +1 = r2.SortOrder AND r1.CurrentLevel = @CurrentLevel AND r2.CurrentLevel = @CurrentLevel672  WHERE673    r2.Morphem = r.Morphem AND r2.MorphemType = 'Morphem' AND r2.MorphemSubType IS NULL AND r1.MorphemType = 'Keyword' AND r1.Morphem = 'OPEN'674  ) AND EXISTS(675  SELECT TOP 1 1676  FROM @Result r1677  JOIN @result r2678    ON r1.SortOrder +1 = r2.SortOrder AND r1.CurrentLevel = @CurrentLevel AND r2.CurrentLevel = @CurrentLevel679  WHERE680    r2.Morphem = r.Morphem AND r2.MorphemType = 'Morphem' AND r2.MorphemSubType IS NULL AND r1.MorphemType = 'Keyword' AND r1.Morphem = 'FROM'681  ) AND EXISTS(682  SELECT TOP 1 1683  FROM @Result r1684  JOIN @result r2685    ON r1.SortOrder +1 = r2.SortOrder AND r1.CurrentLevel = @CurrentLevel AND r2.CurrentLevel = @CurrentLevel686  WHERE687    r2.Morphem = r.Morphem AND r2.MorphemType = 'Morphem' AND r2.MorphemSubType IS NULL AND r1.MorphemType = 'Keyword' AND r1.Morphem = 'Close'688  ) AND EXISTS(689  SELECT TOP 1 1690  FROM @Result r1691  JOIN @result r2692    ON r1.SortOrder +1 = r2.SortOrder AND r1.CurrentLevel = @CurrentLevel AND r2.CurrentLevel = @CurrentLevel693  WHERE694    r2.Morphem = r.Morphem AND r2.MorphemType = 'Morphem' AND r2.MorphemSubType IS NULL AND r1.MorphemType = 'Keyword' AND r1.Morphem = 'deallocate'695  ) AND r.CurrentLevel = @CurrentLevel696  UPDATE @result697  SET Morphemtype = 'KeyWord',698  MorphemSubtype = x.KeywordArea699  FROM @Result r700  JOIN dbo.QBM_FTSQLKeywords(1) x701    ON r.MorphemType = 'Morphem' AND r.MorphemSubtype IS NULL AND r.Morphem = x.Keyword702  WHERE703    r.CurrentLevel = @CurrentLevel704  UPDATE @result705  SET isConflicted = 1706  FROM @Result r707  JOIN dbo.QBM_FTSQLKeywords(1) x708    ON r.MorphemType <> 'KeyWord' AND r.Morphem = x.Keyword709  WHERE710    r.Morphemtype NOT IN('SystemObject') AND r.CurrentLevel = @CurrentLevel endLabel:711  INSERT INTO @erg(SortOrder,712  MorphemType,713  MorphemSubType,714  isConflicted,715  CurrentLevel,716  Morphem,717  p1,718  p2,719  LenMorphem,720  TargetLine)721  SELECT722    SortOrder,723    MorphemType,724    MorphemSubType,725    isConflicted,726    CurrentLevel,727    Morphem,728    p1,729    p2,730    LenMorphem,731    TargetLine732  FROM @result733  RETURN734END
Open raw exported source
SQL · Raw113 lines
1create function dbo.QBM_FCVStringToListSQLMo_int(@SQLIn nvarchar(max) , @CurrentLevel int , @Check int      , @withLiteralTopDown bit ) returns @erg table2 ( SortOrder int , MorphemType nvarchar(16) collate Database_default , MorphemSubType nvarchar(16) collate Database_default , isConflicted bit default 30 , CurrentLevel int default 0 , Morphem nvarchar(max) collate Database_default , p1 nvarchar(256) collate Database_default  , p2 nvarchar(256) collate4 Database_default  , LenMorphem int not null , TargetLine int not null  primary key (SortOrder) ) as begin declare @Element nvarchar(max) declare @i int5 declare @len int declare @Zustand int declare @Nesting int  declare @NextChar nvarchar(1) declare @Next2Char nvarchar(2)  declare @MaxlenAlias int = 106 declare @SourceCode nvarchar(max) select @SourceCode = concat(@sqlIn , nchar(13) , nchar(10)) select @Element = N'' select @len = len(@SourceCode) declare7 @TargetLine int = 0 declare @LineLength int = 0 declare @LineLengthMax int = 150 declare @result QBM_YMorphemInformation  declare @TwoCharMorphem QBM_YSingleGUID8 insert into @TwoCharMorphem(UID_SingleGuid )  values ('+=') , ('-=') , ('<>') , ('!=') , ('<=') , ('>=') declare @OneCharMorphem QBM_YSingleGUID insert9 into @OneCharMorphem(UID_SingleGuid )   Values ( '(') ,(')') ,('=') ,(',') ,(';') ,('+') ,('-') ,('*') ,('/') ,('%') ,('<') ,('>') ,(':') ,('^') ,('!'10) ,('~') ,('&') ,('|') select @Zustand = 0  select @i = 1 while @i <= @len begin select @Next2Char = substring(@SourceCode , @i, 2) if len(@Next2Char) 11< 2 begin select @Next2Char = left(concat(@Next2Char , nchar(13) , nchar(10)), 2) end select @NextChar = left(@Next2Char, 1)  if @Zustand = 0  begin if12 @Next2Char = N'--' begin select @Zustand = 1 select @i += 2 continue end if @Next2Char = N'/*' begin select @Zustand = 2 select @Nesting = 1 select @i13 += 2 continue end if @Next2Char = N'N''' begin select @Zustand = 3 select @Element += @Next2Char select @i += 2 continue end if @NextChar = N'''' begin14 select @Zustand = 3 select @Element += @NextChar select @i += 1 continue end if @next2char in (select UID_SingleGuid  from @TwoCharMorphem) begin  insert15 into @result(Morphemtype, MorphemSubType, morphem, CurrentLevel, LenMorphem, TargetLine) values( 'Morphem' , '2 Chars', @next2char, @CurrentLevel, len16(@Next2Char), @TargetLine)  select @LineLength += len(@Next2Char) +1 if @LineLength > @LineLengthMax begin select @LineLength = 0 select @TargetLine +=17 1 end  select @i += 2 select @Zustand = 0 select @element = N''  continue end if @nextChar in (select UID_SingleGuid  from @OneCharMorphem) begin  insert18 into @result(Morphemtype, MorphemSubType, morphem, CurrentLevel, LenMorphem, TargetLine) values( 'Morphem', '1 Char', @NextChar, @CurrentLevel, len(@NextChar19), @TargetLine)  select @LineLength += len(@NextChar)+1 if @LineLength > @LineLengthMax begin select @LineLength = 0 select @TargetLine += 1 end  select20 @i += 1 select @Zustand = 0 select @element = N''  continue end if @NextChar in (N' ', nchar(9)) begin select @Zustand = 4  select @i += 1 continue end21  if @NextChar = nchar(13) begin  select @i += 1 continue end if @NextChar = nchar(10) begin select @i += 1 continue end  select @Zustand = 5 select @i22 += 1 select @Element += @NextChar continue end   if @Zustand = 1 begin if @nextchar in ( nchar(13), nchar(10)) begin select @Zustand = 0 continue end 23select @i += 1 continue end   if @Zustand = 2 begin if @next2char = N'/*' begin select @Nesting += 1 select @i = @i+2 continue end if @next2char = N'*/'24 begin select @Nesting -= 1 if @nesting = 0 begin select @Zustand = 0 end select @i = @i+2 continue end  select @i += 1 continue end   if @Zustand = 3 25begin if @Next2Char = N'''''' begin select @Element += @next2char select @i += 2 continue end if @nextchar = N'''' begin select @Zustand = 0 select @i 26+= 1 select @Element += @nextchar  insert into @result(Morphemtype,MorphemSubType, morphem, CurrentLevel, LenMorphem, TargetLine) values( 'Literal', 'String'27, @Element, @CurrentLevel, len(@Element), @TargetLine)  select @LineLength += len(@Element)+1 if @LineLength > @LineLengthMax begin select @LineLength 28= 0 select @TargetLine += 1 end   if @withLiteralTopDown = 1 begin if left(@element, 2) = 'N''' begin select @element = substring(@element, 3,len(@element29) - 3) end else begin select @element = substring(@element, 2,len(@element) - 2) end select @element = replace(@element, '''''', '''')  insert into @result30(Morphemtype, MorphemSubType, morphem, CurrentLevel, LenMorphem, TargetLine) select x.Morphemtype, x.MorphemSubType, x.morphem, x.CurrentLevel, len(x.morphem31), @TargetLine from dbo.QBM_FCVStringToListSQLMo_int (@element , @CurrentLevel + 1, @Check, @withLiteralTopDown) x    end select @element = N'' continue32 end  select @i += 1 select @Element += @nextchar continue end   if @Zustand = 4 begin if @nextchar in (N' ', nchar(9), nchar(13), nchar(10) ) begin select33 @i += 1  continue end  select @zustand = 0 continue end   if @zustand = 5 begin if @next2Char in (N'--', N'/*') or @nextchar in (N'''', nchar(10), nchar34(13), N' ', nchar(9)) or @next2char in (select UID_SingleGuid  from @TwoCharMorphem) or @nextChar in (select UID_SingleGuid  from @OneCharMorphem) begin35  select @Zustand = 0  insert into @result(Morphemtype, MorphemSubType, morphem, CurrentLevel, LenMorphem, TargetLine) values( 'Morphem', null, @Element36, @CurrentLevel, len(@element), @TargetLine)  select @LineLength += len(@Element)+1 if @LineLength > @LineLengthMax begin select @LineLength = 0 select37 @TargetLine += 1 end  select @element = N''  continue end  select @i += 1 select @Element += @nextchar continue end    end  if @Check = -1 begin goto 38EndLabel end  update @result set p1 = substring(Morphem, 1, PATINDEX(N'%..%', Morphem)-1) , p2 = substring(Morphem, PATINDEX(N'%..%', Morphem)+2, 256) 39 where morphemtype = 'Morphem' and morphemsubtype is null and PATINDEX(N'%..%', Morphem) > 0 and CurrentLevel = @CurrentLevel update @result set p1 = substring40(Morphem, 1, PATINDEX(N'%.%', Morphem)-1) , p2 = substring(Morphem, PATINDEX(N'%.%', Morphem)+1, 256)  where morphemtype = 'Morphem' and morphemsubtype41 is null and p1 is null and PATINDEX(N'%.%', Morphem) > 0 and CurrentLevel = @CurrentLevel  update @result set MorphemType = 'Literal' , MorphemSubType42 = 'Float' , p1 = null ,p2 = null where morphemtype = 'Morphem' and morphemSubtype is null and isnumeric(p1) = 1 and isnumeric(p2) = 1 and CurrentLevel43 = @CurrentLevel update @result set morphemType = 'Literal' , MorphemSubtype = 'Int' where morphemtype = 'Morphem' and isnumeric(morphem) = 1 and morphemSubtype44 is null and CurrentLevel = @CurrentLevel update @result set morphemType = 'Literal' , MorphemSubtype = 'binary' where morphemtype = 'Morphem' and morphem45 like '0' + 'x%' and morphemSubtype is null and CurrentLevel = @CurrentLevel  update @result set morphemType = 'Variable' , MorphemSubtype = '' where morphemtype46 = 'Morphem' and left(Morphem, 1) = '@' and substring(Morphem, 2,1) <> '@' and CurrentLevel = @CurrentLevel if @Check = 0 begin goto endLabel end  update47 @result set Morphemtype = 'KeyWord' , MorphemSubtype = x.KeywordArea from @Result r join dbo.QBM_FTSQLKeywords(1) x on r.MorphemType = 'Morphem' and r.MorphemSubtype48 is null and r.Morphem = x.Keyword and x.KeywordArea = 'T-SQL' where r.CurrentLevel = @CurrentLevel update @result set Morphemtype = case when x.system_type_id49 = x.user_type_id then 'SystemObject' else 'SchemaObject' end , MorphemSubtype = 'Type' from @Result r join sys.types x on r.MorphemType = 'Morphem' and50 r.MorphemSubtype is null and (r.Morphem = x.name or r.p2 = x.name ) where r.CurrentLevel = @CurrentLevel if @Check = 2 begin goto EndLabel end update 51@result set Morphemtype = 'SchemaObject' , MorphemSubtype = 'Table' from @Result r join sys.tables x with (readpast) on r.MorphemType = 'Morphem' and r.MorphemSubtype52 is null and (r.Morphem = x.name or r.p2 = x.name ) where r.CurrentLevel = @CurrentLevel  update @result set Morphemtype = 'SchemaObject' , MorphemSubtype53 = 'Table' from @Result r where r.MorphemType = 'Morphem' and r.MorphemSubtype is null and (r.Morphem like '#%' or r.p2 like '#%' ) and r.CurrentLevel 54= @CurrentLevel update @result set Morphemtype = 'SchemaObject' , MorphemSubtype = 'View' from @Result r join sys.views x with (readpast) on r.MorphemType55 = 'Morphem' and r.MorphemSubtype is null and (r.Morphem = x.name or r.p2 = x.name ) where r.CurrentLevel = @CurrentLevel update @result set Morphemtype56 = 'SchemaObject' , MorphemSubtype = 'TableFunction' from @Result r join sys.objects x with (readpast) on r.MorphemType = 'Morphem' and r.MorphemSubtype57 is null and (r.Morphem = x.name or r.p2 = x.name ) where x.type in ('IF', 'TF') and r.CurrentLevel = @CurrentLevel update @result set Morphemtype = 'SchemaObject'58 , MorphemSubtype = 'Function' from @Result r join sys.objects x with (readpast) on r.MorphemType = 'Morphem' and r.MorphemSubtype is null and (r.Morphem59 = x.name or r.p2 = x.name ) where x.type in ('FN') and r.CurrentLevel = @CurrentLevel update @result set Morphemtype = 'SchemaObject' , MorphemSubtype60 = 'Procedure' from @Result r join sys.objects x with (readpast) on r.MorphemType = 'Morphem' and r.MorphemSubtype is null and (r.Morphem = x.name or r.p261 = x.name ) where x.type in ('P') and r.CurrentLevel = @CurrentLevel  update @result set morphemtype = 'SystemObject' , MorphemSubType = 'Table' where 62p1 in (select name from sys.schemas with (readpast) ) and p1 <> 'dbo' and p2 > ' ' and CurrentLevel = @CurrentLevel and p2 not like 'sp[_]%'  and morphemtype63 = 'Morphem' and morphemsubtype is null update @result set morphemtype = 'SystemObject' , MorphemSubType = 'Table' where exists (select top 1 1 from sys.databases64 sd  where p1 = sd.name collate database_default ) and p2 > ' ' and CurrentLevel = @CurrentLevel and p2 not like 'sp[_]%'  and morphemtype = 'Morphem' 65and morphemsubtype is null  update @result set MorphemSubtype = 'Table' where MorphemType = 'Variable' and morphem in (select r1.Morphem from @Result r166 join @Result r2 on r1.SortOrder + 1 = r2.SortOrder and r1.Morphemtype = 'Variable' and r2.Morphem = 'Table' join @Result r3 on r2.SortOrder + 1 = r3.SortOrder67 and r3.Morphem = '(' ) and CurrentLevel = @CurrentLevel update @result set Morphemtype = 'ProgramElement' , MorphemSubtype = 'Label' from @Result r join68 (select * from @result where CurrentLevel = @CurrentLevel) as r2 on r.SortOrder +1 = r2.SortOrder where r.MorphemType = 'Morphem' and r.MorphemSubtype69 is null and r2.Morphem = ':' and r.CurrentLevel = @CurrentLevel update @result set Morphemtype = 'ProgramElement' , MorphemSubtype = 'Label' from @Result70 r2 join (select * from @result where CurrentLevel = @CurrentLevel) as r1 on r1.SortOrder +1 = r2.SortOrder where r2.MorphemType = 'Morphem' and r2.MorphemSubtype71 is null and r1.Morphem = 'Goto' and r2.CurrentLevel = @CurrentLevel   update @result set Morphemtype = r1.Morphemtype , MorphemSubtype = 'TableAlias1'72 from @Result r2 join (select * from @result where CurrentLevel = @CurrentLevel) as r1 on r1.morphemSubtype in ('Table', 'view', 'TableFunction') and r1.SortOrder73 + 1 = r2.SortOrder where r2.MorphemType = 'Morphem' and r2.MorphemSubtype is null and len(r2.morphem) <= @MaxlenAlias and r2.p1 is null and r2.CurrentLevel74 = @CurrentLevel  update @result set Morphemtype = r1.Morphemtype , MorphemSubtype = 'TableAlias2' from @Result r3 join (select * from @result where CurrentLevel75 = @CurrentLevel) as r1 on r1.morphemSubtype in ('Table', 'view', 'TableFunction') join (select * from @result where CurrentLevel = @CurrentLevel) as r276 on r2.Morphem = 'AS' and r1.SortOrder + 1 = r2.SortOrder and r2.SortOrder +1 = r3.SortOrder where r3.MorphemType = 'Morphem' and r3.MorphemSubtype is 77null and len(r3.morphem) <= @MaxlenAlias and r3.p1 is null and r3.CurrentLevel = @CurrentLevel  update @result set Morphemtype = r1.Morphemtype , MorphemSubtype78 = 'TableAlias3' from @Result r2 join (select * from @result where CurrentLevel = @CurrentLevel) r1 on r1.morphem = ')' and r1.SortOrder + 1 = r2.SortOrder79 where r2.MorphemType = 'Morphem' and r2.MorphemSubtype is null and len(r2.morphem) <= @MaxlenAlias and r2.p1 is null and r2.CurrentLevel = @CurrentLevel80 and r2.morphem <> 'returns'  update @result set Morphemtype = r1.Morphemtype , MorphemSubtype = 'TableAlias4' from @Result r3 join (select * from @result81 where CurrentLevel = @CurrentLevel) as r1 on r1.morphem = ')' join (select * from @result where CurrentLevel = @CurrentLevel) as r2 on r2.Morphem = 'AS'82 and r1.SortOrder + 1 = r2.SortOrder and r2.SortOrder +1 = r3.SortOrder where r3.MorphemType = 'Morphem' and r3.MorphemSubtype is null and len(r3.morphem83) <= @MaxlenAlias and r3.p1 is null and r3.CurrentLevel = @CurrentLevel  update @Result set Morphemtype = b.Morphemtype , MorphemSubtype = 'TableAlias5'84 from @Result a join (select * from @result where CurrentLevel = @CurrentLevel) as b on b.MorphemSubtype like 'TableAlias_' and b.Morphem = a.Morphem where85 a.MorphemType = 'Morphem' and a.MorphemSubtype is null and not exists (select top 1 1 from @Result c where c.Morphem = a.Morphem and (c.MorphemSubtype86 not like 'TableAlias_' ) ) and a.CurrentLevel = @CurrentLevel   update @result set Morphemtype = te.Morphemtype , MorphemSubtype = 'Column' from @Result87 r join sys.columns x with (readpast) on r.MorphemType = 'Morphem' and r.MorphemSubtype is null and (r.Morphem = x.name or r.p2 = x.name ) join sys.objects88 t with (readpast) on x.Object_id = t.Object_id join (select * from @result where CurrentLevel = @CurrentLevel) as te on te.MorphemSubtype in ('table',89 'View', 'TableFunction') and ( te.morphem = t.name or te.p2 = t.name ) where r.CurrentLevel = @CurrentLevel  update @result set Morphemtype = ta.Morphemtype90 , MorphemSubtype = 'Column' from @Result r join (select * from @result where CurrentLevel = @CurrentLevel) as ta on ta.MorphemSubtype like ('tableAlias_'91) and r.p1 = ta.morphem where r.CurrentLevel = @CurrentLevel  update @Result set Morphemtype = r1.Morphemtype , MorphemSubtype = 'ColumnAlias' from @Result92 r3 join (select * from @result where CurrentLevel = @CurrentLevel) as r2 on r2.SortOrder +1 = r3.SortOrder join (select * from @result where CurrentLevel93 = @CurrentLevel) as r1 on r1.SortOrder +1 = r2.SortOrder where r1.MorphemSubtype = 'Column' and r2.Morphem = 'as' and r3.MorphemType = 'Morphem' and r3.MorphemSubtype94 is null and r3.CurrentLevel = @CurrentLevel  update @Result set Morphemtype = r1.Morphemtype , MorphemSubtype = 'ColumnAlias' from @Result r2 join (select95 * from @result where CurrentLevel = @CurrentLevel) as r1 on r1.SortOrder +1 = r1.SortOrder where r1.MorphemSubtype = 'Column' and r2.MorphemType = 'Morphem'96 and r2.MorphemSubtype is null and r2.CurrentLevel = @CurrentLevel update @result set Morphemtype = 'ProgramElement' , MorphemSubtype = 'Cur' + 'sor' from97 @Result r where r.MorphemType = 'Morphem' and r.MorphemSubtype is null and exists (select top 1 1 from @Result r1 join @result r2 on r1.SortOrder +1 =98 r2.SortOrder and r1.CurrentLevel = @CurrentLevel and r2.CurrentLevel = @CurrentLevel where r2.Morphem = r.Morphem and r2.MorphemType = 'Morphem' and r2.MorphemSubType99 is null and r1.MorphemType = 'Keyword' and r1.Morphem = 'DECLARE' ) and exists (select top 1 1 from @Result r1 join @result r2 on r1.SortOrder +1 = r2.SortOrder100 and r1.CurrentLevel = @CurrentLevel and r2.CurrentLevel = @CurrentLevel where r2.Morphem = r.Morphem and r2.MorphemType = 'Morphem' and r2.MorphemSubType101 is null and r1.MorphemType = 'Keyword' and r1.Morphem = 'OPEN' ) and exists (select top 1 1 from @Result r1 join @result r2 on r1.SortOrder +1 = r2.SortOrder102 and r1.CurrentLevel = @CurrentLevel and r2.CurrentLevel = @CurrentLevel where r2.Morphem = r.Morphem and r2.MorphemType = 'Morphem' and r2.MorphemSubType103 is null and r1.MorphemType = 'Keyword' and r1.Morphem = 'FROM' ) and exists (select top 1 1 from @Result r1 join @result r2 on r1.SortOrder +1 = r2.SortOrder104 and r1.CurrentLevel = @CurrentLevel and r2.CurrentLevel = @CurrentLevel where r2.Morphem = r.Morphem and r2.MorphemType = 'Morphem' and r2.MorphemSubType105 is null and r1.MorphemType = 'Keyword' and r1.Morphem = 'Close' ) and exists (select top 1 1 from @Result r1 join @result r2 on r1.SortOrder +1 = r2.SortOrder106 and r1.CurrentLevel = @CurrentLevel and r2.CurrentLevel = @CurrentLevel where r2.Morphem = r.Morphem and r2.MorphemType = 'Morphem' and r2.MorphemSubType107 is null and r1.MorphemType = 'Keyword' and r1.Morphem = 'deallocate' ) and r.CurrentLevel = @CurrentLevel  update @result set Morphemtype = 'KeyWord' 108, MorphemSubtype = x.KeywordArea from @Result r join dbo.QBM_FTSQLKeywords(1) x on r.MorphemType = 'Morphem' and r.MorphemSubtype is null and r.Morphem109 = x.Keyword where r.CurrentLevel = @CurrentLevel update @result set isConflicted = 1 from @Result r join dbo.QBM_FTSQLKeywords(1) x on r.MorphemType <>110 'KeyWord' and r.Morphem = x.Keyword where r.Morphemtype not in ('SystemObject') and r.CurrentLevel = @CurrentLevel endLabel: insert into @erg( SortOrder111 , MorphemType , MorphemSubType , isConflicted , CurrentLevel , Morphem , p1 , p2 , LenMorphem , TargetLine ) select SortOrder , MorphemType , MorphemSubType112 , isConflicted , CurrentLevel , Morphem , p1 , p2 , LenMorphem , TargetLine from @result return end 113