Back to OIM Explorer

dbo.QBM_FSQObjectComment

Scalar FunctionSQL_SCALAR_FUNCTIONSandbox DB

Scalar Function.

Source: sandbox-db sys.sql_modules

Source size: 5.520 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_TUIndicator474488 source text reference

References

dbo.QBM_TUIndicator474488

Referenced By

Complete Source

1CREATE FUNCTION dbo.QBM_FSQObjectComment(2  @Objectname nvarchar(64)3) RETURNS nvarchar(max4)5AS6BEGIN7  DECLARE @crlf nvarchar(10) = nchar(13) + nchar(10)8  DECLARE @erg nvarchar(max) = N ''9  DECLARE @Objecttype nvarchar(10) = NULL10  DECLARE @ParameterName nvarchar(64)11  DECLARE @ParameterType nvarchar(64)12  DECLARE @ParameterLen nvarchar(64)13  DECLARE @TableName nvarchar(64)14  DECLARE @operation nvarchar(64)15  DECLARE @ReturnValue nvarchar(max) = N ''16  DECLARE @ElementCount int17  DECLARE @ElementIndex int18  DECLARE @ElementLast int19  DECLARE @ElementBufferMulti QBM_YCursorBuffer20  SELECT TOP 1 @Objecttype = type21  FROM sys.objects22    WITH(readpast)23  WHERE24    name = @Objectname25  IF @Objecttype IS NULL26  BEGIN27    GOTO ende28  END29  IF @Objecttype = N 'TR'30  BEGIN31    SELECT32      TOP 1 @TableName = ta.name,33      @operation = ev.type_desc34    FROM sys.triggers t35      WITH(readpast)36    JOIN sys.objects ta37      WITH(readpast)38      ON t.parent_id = ta.object_id39    JOIN sys.trigger_events ev40      WITH(readpast)41      ON ev.object_id = t.object_id42    WHERE43      t.name = @Objectname AND t.name != 'QBM_TUIndicator474488'44    SELECT45      @erg = @erg + @crlf + N '---<summary>:DE:' + @operation + N '-Trigger für Tabelle ' + @TableName + N ' </summary>'46  END47  ELSE48  BEGIN49    SELECT50      @erg = @erg + @crlf + N '---<summary>:DE:' + N 'T' + N 'O' + N 'D' + N 'O' + N ': allgmeine Beschreibung von ' + @Objectname + N ' </summary>'51  END52  DELETE @ElementBufferMulti53  INSERT INTO @ElementBufferMulti(LongIdent1,54  LongIdent2,55  Ident1)56  SELECT57    PARAMETER_NAME,58    DATA_TYPE,59  CASE CHARACTER_MAXIMUM_LENGTH60    WHEN NULL THEN61  N ''62    WHEN -1 THEN63  N 'max'64  ELSE convert(nvarchar(16),65  CHARACTER_MAXIMUM_LENGTH)66  END67  FROM information_schema.parameters68    WITH(readpast)69  WHERE70    specific_name = @Objectname71  ORDER BY Ordinal_position72  SELECT @ElementCount = @@ROWCOUNT73  SELECT @ElementIndex = @@IDENTITY - @ElementCount +174  SELECT @ElementLast = @@IDENTITY75  WHILE @ElementIndex <= @ElementLast76  BEGIN77    SELECT78      TOP 1 @parameterName = bu.LongIdent1,79      @parameterType = bu.LongIdent2,80      @parameterLen = bu.Ident181    FROM @ElementBufferMulti bu82    WHERE83      bu.ElementIndex = @ElementIndex84    IF85    LEFT(@parametername,86    1) = N '@'87    BEGIN88      SELECT89        @parametername = substring(@parametername,90        2,91        255)92    END93    IF @parameterlen > N ''94    BEGIN95      SELECT @parametertype = @parametertype + N '(' + @parameterlen + N ')'96    END97    IF @parametername > ' '98    BEGIN99      SELECT100        @erg = @erg + @crlf + N '---<param name="' + @parameterName + '" type="' + @parametertype + '">' + N 'T' + N 'O' + N 'D' + N 'O' + N ': Parameter-Beschreibung einfügen</param>'101    END102    ELSE103    BEGIN104      SELECT105        @ReturnValue = N '---<returns Type="' + @parametertype + '">' + N 'T' + N 'O' + N 'D' + N 'O' + N ': ReturnValue-Beschreibung einfügen</returns>'106    END107    SELECT @ElementIndex += 1108  END109  IF @ReturnValue > ' '110  BEGIN111    SELECT @erg = @erg + @crlf + @returnvalue112  END113  ELSE114  BEGIN115    DELETE @ElementBufferMulti116    INSERT INTO @ElementBufferMulti(LongIdent1,117    LongIdent2,118    Ident1)119    SELECT120      c.name,121      t.name,122      c.max_length / 2123    FROM sys.columns c124      WITH(readpast)125    JOIN sys.types t126      WITH(readpast)127      ON c.user_type_id = t.user_type_id128    WHERE129      c.object_id = object_id(@Objectname) AND NOT EXISTS(130    SELECT TOP 1 1131    FROM information_schema.parameters p132      WITH(readpast)133    WHERE134      p.specific_name = @Objectname AND p.PARAMETER_NAME = c.name)135    ORDER BY c.column_id136    SELECT @ElementCount = @@ROWCOUNT137    SELECT @ElementIndex = @@IDENTITY - @ElementCount +1138    SELECT @ElementLast = @@IDENTITY139    WHILE @ElementIndex <= @ElementLast140    BEGIN141      SELECT142        TOP 1 @parameterName = bu.LongIdent1,143        @parameterType = bu.LongIdent2,144        @parameterLen = bu.Ident1145      FROM @ElementBufferMulti bu146      WHERE147        bu.ElementIndex = @ElementIndex148      SELECT149        @ReturnValue = @ReturnValue + @crlf + N '---<tr><td>' + @ParameterName + N '</td><td>' + @parameterType + N '</td><td>' + @parameterlen150        + N '</td></tr>'151      SELECT @ElementIndex += 1152    END153    IF @returnValue > ' '154    BEGIN155      SELECT156        @erg = @erg + @crlf + N '---<returns>' + @crlf + N '---<table><tr><th>column name</th><th>data type</th><th>length</th></tr>' + @returnvalue157        + N '' + @crlf + N '---</table>' + @crlf + N '---</returns>'158    END159  END160  IF @Objecttype = N 'TR'161  BEGIN162    SELECT163      @erg = @erg + N '' + @crlf + N '---<remarks>Einstellen der DBScheduler-Operation ' + N 'T' + N 'O' + N 'D' + N 'O' + N ': ' + @crlf + N164      '---</remarks>'165  END166  ELSE167  BEGIN168    SELECT169      @erg = @erg + N '' + @crlf + N '---<remarks>' + N 'T' + N 'O' + N 'D' + N 'O' + N ': Bemerkungen einfügen</remarks>' + @crlf + N '---<example>'170      + @crlf + N '---<code>' + @crlf + N '---' + N 'T' + N 'O' + N 'D' + N 'O' + N ': Aufrufbeispiel eintragen' + @crlf + N '---</code></example>'171  END172  DELETE @ElementBufferMulti173  INSERT INTO @ElementBufferMulti(LongIdent1,174  LongIdent2)175  SELECT176    DISTINCT p.name,177  CASE p.type178    WHEN 'P' THEN179  'Procedure'180    WHEN 'FN' THEN181  'Function'182    WHEN 'TF' THEN183  'Table-Function'184    WHEN 'IF' THEN185  'Table-Function'186    WHEN 'V' THEN187  'View'188  ELSE p.type189  END190  FROM sys.sql_dependencies d191    WITH(readpast)192  JOIN sys.objects o193    WITH(readpast)194    ON d.object_id = o.object_id195  JOIN sys.objects p196    WITH(readpast)197    ON d.referenced_major_id = p.object_id198  WHERE199    o.object_id = object_id(@Objectname) AND p.type NOT IN(N 'U',200  N 'V')201  ORDER BY p.name202  SELECT @ElementCount = @@ROWCOUNT203  SELECT @ElementIndex = @@IDENTITY - @ElementCount +1204  SELECT @ElementLast = @@IDENTITY205  WHILE @ElementIndex <= @ElementLast206  BEGIN207    SELECT208      TOP 1 @ParameterName = bu.LongIdent1,209      @Parametertype = bu.LongIdent2210    FROM @ElementBufferMulti bu211    WHERE212      bu.ElementIndex = @ElementIndex213    SELECT214      @erg = @erg + @crlf + N '---<seealso cref="' +@parametername + N '" type="' +@parametertype + N '">' + @Parametertype + N ' ' + @parametername215      + N '</seealso>'216    SELECT @ElementIndex += 1217  END218  ende:219  RETURN(@erg)220END

Open raw exported source

1    create   function dbo.QBM_FSQObjectComment (@Objectname nvarchar(64) ) returns nvarchar(max)  as begin declare @crlf nvarchar(10) = nchar(132) + nchar(10) declare @erg nvarchar(max) = N'' declare @Objecttype nvarchar(10) = null declare @ParameterName nvarchar(64) declare @ParameterType nvarchar3(64) declare @ParameterLen nvarchar(64) declare @TableName nvarchar(64) declare @operation nvarchar(64) declare @ReturnValue nvarchar(max) = N'' declare4 @ElementCount int declare @ElementIndex int declare @ElementLast int declare @ElementBufferMulti QBM_YCursorBuffer select top 1 @Objecttype = type from5 sys.objects with (readpast) where name = @Objectname if @Objecttype is null begin goto ende end if @Objecttype = N'TR' begin select top 1 @TableName =6 ta.name, @operation = ev.type_desc from sys.triggers t with (readpast) join sys.objects ta with (readpast) on t.parent_id = ta.object_id join sys.trigger_events7 ev with (readpast) on ev.object_id = t.object_id where t.name = @Objectname and t.name != 'QBM_TUIndicator474488' select @erg = @erg + @crlf + N'---<summary>:DE:'8 + @operation + N'-Trigger für Tabelle ' + @TableName + N' </summary>' end else begin select @erg = @erg + @crlf + N'---<summary>:DE:'+ N'T' + N'O' + N'D'9 + N'O'+ N': allgmeine Beschreibung von ' + @Objectname + N' </summary>' end  delete  @ElementBufferMulti insert into @ElementBufferMulti (LongIdent1, 10LongIdent2, Ident1)  select PARAMETER_NAME , DATA_TYPE, case CHARACTER_MAXIMUM_LENGTH when null then N'' when -1 then N'max' else convert(nvarchar(16),11 CHARACTER_MAXIMUM_LENGTH) end from information_schema.parameters with (readpast) where specific_name = @Objectname order by Ordinal_position select @ElementCount12 = @@ROWCOUNT select @ElementIndex = @@IDENTITY - @ElementCount +1 select @ElementLast = @@IDENTITY while @ElementIndex <= @ElementLast begin select top13 1 @parameterName = bu.LongIdent1 , @parameterType = bu.LongIdent2 , @parameterLen = bu.Ident1 from @ElementBufferMulti bu where bu.ElementIndex = @ElementIndex14 if left(@parametername, 1) = N'@' begin select @parametername = substring(@parametername, 2, 255) end  if @parameterlen > N'' begin select @parametertype15 = @parametertype + N'(' + @parameterlen + N')' end if @parametername > ' ' begin select @erg = @erg + @crlf + N'---<param name="' + @parameterName + '" type="'16 + @parametertype + '">'+ N'T' + N'O' + N'D' + N'O'+ N': Parameter-Beschreibung einfügen</param>' end else begin  select @ReturnValue = N'---<returns Type="'17 + @parametertype + '">'+ N'T' + N'O' + N'D' + N'O'+ N': ReturnValue-Beschreibung einfügen</returns>' end select @ElementIndex += 1 end  if @ReturnValue18 > ' ' begin select @erg = @erg + @crlf + @returnvalue end else begin  delete  @ElementBufferMulti insert into @ElementBufferMulti (LongIdent1, LongIdent219, Ident1) select c.name , t.name, c.max_length / 2 from sys.columns c with (readpast) join sys.types t with (readpast) on c.user_type_id = t.user_type_id20 where c.object_id = object_id(@Objectname)  and not exists (select top 1 1 from information_schema.parameters p with (readpast) where p.specific_name 21= @Objectname and p.PARAMETER_NAME = c.name ) order by c.column_id select @ElementCount = @@ROWCOUNT select @ElementIndex = @@IDENTITY - @ElementCount 22+1 select @ElementLast = @@IDENTITY while @ElementIndex <= @ElementLast begin select top 1 @parameterName = bu.LongIdent1 , @parameterType = bu.LongIdent223 , @parameterLen = bu.Ident1 from @ElementBufferMulti bu where bu.ElementIndex = @ElementIndex  select @ReturnValue = @ReturnValue + @crlf + N'---<tr><td>'24 + @ParameterName + N'</td><td>' + @parameterType + N'</td><td>' + @parameterlen + N'</td></tr>' select @ElementIndex += 1 end   if @returnValue > ' ' 25begin select @erg = @erg + @crlf + N'---<returns>'+ @crlf + N'---<table><tr><th>column name</th><th>data type</th><th>length</th></tr>' + @returnvalue 26+ N''+ @crlf + N'---</table>'+ @crlf + N'---</returns>' end end if @Objecttype = N'TR' begin  select @erg = @erg + N''+ @crlf + N'---<remarks>Einstellen der DBScheduler-Operation '27+ N'T' + N'O' + N'D' + N'O'+ N': ' + @crlf + N'---</remarks>' end else begin  select @erg = @erg + N''+ @crlf + N'---<remarks>'+ N'T' + N'O' + N'D' + N'O'28+ N': Bemerkungen einfügen</remarks>'+ @crlf + N'---<example>'+ @crlf + N'---<code>'+ @crlf + N'---'+ N'T' + N'O' + N'D' + N'O'+ N': Aufrufbeispiel eintragen'29+ @crlf + N'---</code></example>' end  delete  @ElementBufferMulti insert into @ElementBufferMulti (LongIdent1, LongIdent2) select distinct p.name, case30 p.type when 'P' then 'Procedure' when 'FN' then 'Function' when 'TF' then 'Table-Function' when 'IF' then 'Table-Function' when 'V' then 'View' else p.type31 end from sys.sql_dependencies d with (readpast) join sys.objects o with (readpast) on d.object_id = o.object_id join sys.objects p with (readpast) on 32d.referenced_major_id = p.object_id where o.object_id = object_id(@Objectname) and p.type not in (N'U', N'V') order by p.name select @ElementCount = @@ROWCOUNT33 select @ElementIndex = @@IDENTITY - @ElementCount +1 select @ElementLast = @@IDENTITY while @ElementIndex <= @ElementLast begin select top 1 @ParameterName34 = bu.LongIdent1 , @Parametertype = bu.LongIdent2 from @ElementBufferMulti bu where bu.ElementIndex = @ElementIndex select @erg = @erg + @crlf + N'---<seealso cref="'35 +@parametername + N'" type="' +@parametertype + N'">' + @Parametertype + N' ' + @parametername + N'</seealso>' select @ElementIndex += 1 end  ende: return36(@erg) end 37