Back to OIM Explorer

dbo.QBM_FSQColumnList

Scalar FunctionSQL_SCALAR_FUNCTIONSandbox DB

Scalar Function.

Source: sandbox-db sys.sql_modules

Source size: 1.563 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_FCVGuidToReplace138 source text reference
  • references source dbo.QBM_FCVGuidToReplace38 source text reference
  • references source dbo.QBM_FCVGuidToReplaceMax source text reference

References

Referenced By

Complete Source

SQL58 lines
1CREATE FUNCTION dbo.QBM_FSQColumnList(2  @TableName varchar(30),3  @WithReplaceCall BIT,4  @SuppressAutomaticColumns BIT5) RETURNS nvarchar(max6)7AS8BEGIN9  RETURN(10  SELECT11    string_agg(z.EineZeile, nchar(13) + nchar(10) + nchar(9) + N ', ') within12    GROUP(13  ORDER BY z.Zeile)14  FROM(15  SELECT16    string_agg(convert(nvarchar(max), y.ColumnName), N ', ') within17    GROUP(18  ORDER BY y.column_id) AS EineZeile, y.Zeile19  FROM(20  SELECT21    x.Zeile, x.column_id, CASE22    WHEN @WithReplaceCall = 0 OR x.DATA_TYPE NOT LIKE '%char%' OR isnull(x.SchemaDatalen, 0) < 38 THEN23    x.ColumnName24  ELSE CONCAT(CASE x.SchemaDatalen25  WHEN 38 THEN26  'dbo.QBM_FCVGuidToReplace38('27  WHEN 138 THEN28  'dbo.QBM_FCVGuidToReplace138('29  WHEN 263 THEN30  'dbo.QBM_FCVGuidToReplace138('31  ELSE 'dbo.QBM_FCVGuidToReplaceMax('32  END, x.ColumnName, ', ''',(33  SELECT TOP 1 uid_database34  FROM DialogDatabase35    WITH(readpast)36  WHERE37    IsMainDatabase = 1), ''')')38  END AS ColumnName39  FROM(40  SELECT row_number() over(41  ORDER BY c.column_id) / 10 AS Zeile, c.column_id, c.name AS Columnname, cc.DATA_TYPE, CASE cc.CHARACTER_MAXIMUM_LENGTH42  WHEN -1 THEN43  200000044  ELSE cc.CHARACTER_MAXIMUM_LENGTH45  END AS SchemaDatalen46  FROM sys.tables t47    WITH(readpast)48  JOIN sys.columns c49    WITH(readpast)50    ON t.object_id = c.object_id51  JOIN INFORMATION_SCHEMA.COLUMNS cc52    WITH(readpast)53    ON cc.TABLE_NAME = t.name AND cc.COLUMN_NAME = c.name54  WHERE55    t.name = @TableName AND((@SuppressAutomaticColumns = 1 AND cc.DATA_TYPE NOT IN('timestamp', 'rowversion')) OR @SuppressAutomaticColumns56  = 0)) AS x) AS y57  GROUP BY y.Zeile) AS z)58END
Open raw exported source
SQL ยท Raw12 lines
1  create   function dbo.QBM_FSQColumnList(@TableName varchar(30) , @WithReplaceCall bit   , @SuppressAutomaticColumns bit ) returns nvarchar(max2)  as begin return ( select string_agg(z.EineZeile , nchar(13) + nchar(10) + nchar(9) + N', ' )  within group (order by z.Zeile) from ( select string_agg3(convert(nvarchar(max), y.ColumnName )  , N', ' )  within group(order by y.column_id) as EineZeile , y.Zeile from( select x.Zeile, x.column_id , case when4 @WithReplaceCall = 0 or x.DATA_TYPE not like '%char%' or isnull(x.SchemaDatalen, 0) < 38 then x.ColumnName else concat(case x.SchemaDatalen when 38 then5 'dbo.QBM_FCVGuidToReplace38(' when 138 then 'dbo.QBM_FCVGuidToReplace138(' when 263 then 'dbo.QBM_FCVGuidToReplace138(' else 'dbo.QBM_FCVGuidToReplaceMax('6 end , x.ColumnName , ', ''' , ( select top 1 uid_database from DialogDatabase with (readpast) where IsMainDatabase = 1 ) , ''')' ) end as ColumnName from7 ( select row_number() over (order by c.column_id) / 10 as Zeile ,c.column_id , c.name as Columnname , cc.DATA_TYPE , case cc.CHARACTER_MAXIMUM_LENGTH 8when -1 then 2000000 else cc.CHARACTER_MAXIMUM_LENGTH end as SchemaDatalen from sys.tables t with (readpast) join sys.columns c with (readpast) on t.object_id9 = c.object_id join INFORMATION_SCHEMA.COLUMNS cc with (readpast) on cc.TABLE_NAME = t.name and cc.COLUMN_NAME = c.name where t.name = @TableName and (10 (@SuppressAutomaticColumns = 1 and cc.DATA_TYPE not in ('timestamp', 'rowversion' ) ) or @SuppressAutomaticColumns = 0 ) ) as x  ) as y group by y.Zeile11 ) as z ) end 12