Back to OIM Explorer

dbo.QBM_FSQColumDef

Scalar FunctionSQL_SCALAR_FUNCTIONSandbox DB

Scalar Function.

Source: sandbox-db sys.sql_modules

Source size: 2.578 characters

Interpretation

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

Relations

  • No extracted relations.

Typed Edges

  • No typed edges extracted for this source.

References

  • No direct source references extracted.

Referenced By

Complete Source

SQL96 lines
1CREATE FUNCTION dbo.QBM_FSQColumDef(2  @TableOrTypeName nvarchar(64),3  @ColumnPattern nvarchar(64)4) RETURNS nvarchar(max5)6AS7BEGIN8  DECLARE @erg nvarchar(max) = ''9  DECLARE @ElementBuffer TABLE(ElementIndex int identity,10  Int1 int DEFAULT 0,11  ContentShort nvarchar(400) collate database_default)12  DECLARE @ElementCount int13  DECLARE @ElementIndex int14  INSERT INTO @ElementBuffer(ContentShort,15  Int1)16  SELECT COLUMN_NAME + ' ' + x.DataType + CASE17  WHEN x.DataType IN('varchar',18  'nvarchar',19  'char',20  'nchar') THEN21  '(' + x.LenDefinition + ') collate database_default'22  WHEN x.DataType IN('varbinary') THEN23  '(' + x.LenDefinition + ')'24  WHEN x.DataType IN('text',25  'ntext') THEN26  '  collate database_default /* die wertart wird nicht mehr unterstützt */ '27  ELSE ''28  END + x.ConvertedDefault + x.NullProperty,29  x.ORDINAL_POSITION30  FROM(31  SELECT32    c.name AS COLUMN_NAME,33    ST.name AS DataType,34  CASE35    WHEN ST.name IN('nchar', 'nvarchar') AND c.max_length <> -1 THEN36  convert(varchar(16), c.max_length / 2)37    WHEN c.max_length = -1 THEN38  'max'39  ELSE convert(varchar(16), c.max_length)40  END AS LenDefinition,41  CASE42    WHEN c.is_identity = 1 AND ST.name IN('int', 'bigint', 'real', 'float') THEN43  ' identity'44    WHEN de.ColumnDefault IS NULL AND ST.name IN('bit', 'int', 'bigint', 'real', 'float') THEN45  ' default 0'46    WHEN de.ColumnDefault IS NULL THEN47  ''48    WHEN de.ColumnDefault IN('((0))', 'create default DezimalNULL as 0') THEN49  ' default 0'50  ELSE ' default ' + SUBSTRING(de.ColumnDefault, 2, LEN(de.ColumnDefault) -2)51  END AS ConvertedDefault,52  CASE C.is_nullable53    WHEN 0 THEN54  ' NOT'55  ELSE ''56  END + ' NULL' AS NullProperty,57  columnproperty(c.object_id, c.name, 'ordinal') AS ORDINAL_POSITION58  FROM(59  SELECT t1.object_id60  FROM sys.objects t161    WITH(readpast)62  WHERE63    t1.name = @TableOrTypeName AND t1.type IN('U', 'V')64  UNION all65  SELECT t2.type_table_object_id66  FROM sys.table_types t267    WITH(readpast)68  WHERE69    t2.name = @TableOrTypeName) AS t70  JOIN sys.columns c71    WITH(readpast)72    ON c.object_id = t.object_id73  JOIN sys.systypes AS ST74    WITH(readpast)75    ON ST.xusertype = c.system_type_id CROSS apply(76  SELECT OBJECT_DEFINITION(c.default_object_id) AS ColumnDefault) AS de77  WHERE78    c.name LIKE @ColumnPattern) AS x79  SELECT @ElementCount = @@ROWCOUNT80  SELECT @ElementIndex = 181  IF @ElementCount = 182  BEGIN83    SELECT TOP 1 @erg = bu.ContentShort84    FROM @ElementBuffer bu85  END86  ELSE87  BEGIN88    SELECT89      TOP 1 @erg = char(9) + '  ' + string_agg(convert(nvarchar(max), bu.ContentShort),90      nchar(13) +nchar(10) +nchar(9) +N ', ') within91      GROUP(92    ORDER BY bu.int1)93    FROM @ElementBuffer bu94  END95  RETURN(@erg)96END
Open raw exported source
SQL · Raw18 lines
1     create   function dbo.QBM_FSQColumDef (@TableOrTypeName nvarchar(64) , @ColumnPattern nvarchar(64) ) returns nvarchar(max)  as begin declare2 @erg nvarchar(max) = '' declare @ElementBuffer  table (ElementIndex int identity , Int1 int default 0 , ContentShort nvarchar(400) collate database_default3 ) declare @ElementCount int declare @ElementIndex int insert into @ElementBuffer (ContentShort, Int1) select COLUMN_NAME + ' ' + x.DataType + case when4 x.DataType in ( 'varchar', 'nvarchar', 'char', 'nchar' ) then '(' + x.LenDefinition + ') collate database_default' when x.DataType in ('varbinary') then5 '(' + x.LenDefinition + ')' when x.DataType in ('text' , 'ntext') then '  collate database_default /* die wertart wird nicht mehr unterstützt */ '   else6 '' end + x.ConvertedDefault + x.NullProperty ,x.ORDINAL_POSITION from ( select c.name as COLUMN_NAME, ST.name as DataType , case when ST.name in ('nchar'7, 'nvarchar') and c.max_length <> -1 then convert(varchar(16) , c.max_length / 2) when c.max_length = -1 then 'max' else convert(varchar(16), c.max_length8) end as LenDefinition , case when c.is_identity = 1 and ST.name in ('int' , 'bigint', 'real' , 'float' ) then ' identity' when de.ColumnDefault is null9 and ST.name in ('bit' , 'int' , 'bigint', 'real' , 'float' ) then ' default 0' when de.ColumnDefault is null then '' when de.ColumnDefault in ('((0))'10, 'create default DezimalNULL as 0') then ' default 0' else ' default ' + SUBSTRING(de.ColumnDefault, 2, LEN(de.ColumnDefault)-2) end as ConvertedDefault11 , case C.is_nullable when 0 then ' NOT' else '' end + ' NULL' as NullProperty , columnproperty(c.object_id, c.name, 'ordinal') as ORDINAL_POSITION from12 ( select t1.object_id from sys.objects t1 with (readpast) where t1.name = @TableOrTypeName and t1.type in ('U', 'V') union all select t2.type_table_object_id13 from sys.table_types t2 with (readpast) where t2.name = @TableOrTypeName ) as t join sys.columns c with (readpast) on c.object_id = t.object_id join sys.systypes14 as ST with (readpast) on ST.xusertype = c.system_type_id cross apply (select OBJECT_DEFINITION(c.default_object_id) as ColumnDefault) as de where c.name15 like @ColumnPattern ) as x  select @ElementCount = @@ROWCOUNT select @ElementIndex = 1 if @ElementCount = 1 begin select top 1 @erg = bu.ContentShort 16from @ElementBuffer bu end else begin select top 1 @erg = char(9) + '  ' + string_agg(convert(nvarchar(max),bu.ContentShort )  , nchar(13)+nchar(10)+nchar17(9)+N', ' )  within group (order by bu.int1) from @ElementBuffer bu end return (@erg) end 18