Back to OIM Explorer

dbo.QBM_FSQIndexDef_i

Scalar FunctionSQL_SCALAR_FUNCTIONSandbox DB

Scalar Function.

Source: sandbox-db sys.sql_modules

Source size: 2.443 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_FSQIndexColumns source text reference
  • references source dbo.QBM_FSQIndexDef source text reference
  • references source dbo.QBM_PIndexCreate source text reference

References

Referenced By

Complete Source

SQL108 lines
1CREATE FUNCTION dbo.QBM_FSQIndexDef_i(2  @TableOrTypeName nvarchar(64),3  @IndexPattern nvarchar(64),4  @IncludeGenerated BIT = 05) RETURNS nvarchar(max6)7AS8BEGIN9  DECLARE @erg nvarchar(max) = ''10  DECLARE @ElementBuffer TABLE(ElementIndex int identity,11  ContentFull nvarchar(max) collate database_default)12  DECLARE @TableType varchar(16) = 'TAB'13  IF EXISTS(14    SELECT TOP 1 115    FROM sys.tables t16    WITH(readpast)17  WHERE18    t.name = @TableOrTypeName AND t.is_memory_optimized = 1)19  BEGIN20    SELECT @TableType = 'MEMTAB'21  END22  IF EXISTS(23    SELECT TOP 1 124    FROM sys.table_types t25    WITH(readpast)26  WHERE27    t.name = @TableOrTypeName)28  BEGIN29    SELECT @TableType = 'TYPE'30  END31  IF @TableType IN('MEMTAB',32  'TYPE')33  BEGIN34    INSERT INTO @ElementBuffer(ContentFull)35    SELECT36      '	, index ' + i.name + CASE37      WHEN h.bucket_count IS NULL THEN38      ''39    ELSE ' hash'40    END + ' (' + dbo.QBM_FSQIndexColumns(@TableOrTypeName,41    i.name,42    0) + ')' + CASE43    WHEN h.bucket_count IS NULL THEN44    ''45    ELSE ' with (BUCKET_COUNT = ' + ltrim(STR(h.bucket_count)) + ')'46    END47    FROM(48    SELECT t1.object_id49    FROM sys.tables t150      WITH(readpast)51    WHERE52      t1.name = @TableOrTypeName53    UNION all54    SELECT t2.type_table_object_id55    FROM sys.table_types t256      WITH(readpast)57    WHERE58      t2.name = @TableOrTypeName) AS t59    JOIN sys.indexes i60      WITH(readpast)61      ON t.object_id = i.object_id AND i.is_primary_key = 0 AND i.is_hypothetical = 0 AND i.type NOT IN(3,62    4,63    5,64    6)65    LEFT66    OUTER67    JOIN sys.hash_indexes h68      WITH(readpast)69      ON h.index_id = i.index_id AND h.object_id = i.object_id70    WHERE71      i.type_desc <> 'HEAP' AND i.name LIKE @IndexPattern72    ORDER BY i.name73  END74  IF @TableType = 'TAB'75  BEGIN76    INSERT INTO @ElementBuffer(ContentFull)77    SELECT78      'exec QBM_PIndexCreate ''' + @TableOrTypeName + ''', ''' + i.name + ''', ' + convert(varchar(16),79      i.is_unique) + ', ''' + dbo.QBM_FSQIndexColumns(@TableOrTypeName,80      i.name,81      0) + ''' ' + CASE82      WHEN dbo.QBM_FSQIndexColumns(@TableOrTypeName,83      i.name,84      1) > ' ' THEN85      ', ''' + dbo.QBM_FSQIndexColumns(@TableOrTypeName,86      i.name,87      1) + ''' '88    ELSE ''89    END90    FROM sys.tables t91      WITH(readpast)92    JOIN sys.indexes i93      WITH(readpast)94      ON t.object_id = i.object_id AND t.name = @TableOrTypeName AND i.name LIKE @IndexPattern95    WHERE96      i.is_primary_key = 0 AND i.is_hypothetical = 0 AND(i.name NOT LIKE 'GEN[_]%' OR @IncludeGenerated = 1) AND i.type NOT IN(3,97    4,98    5,99    6)100  END101  SELECT102    @erg = string_agg(bu.ContentFull,103    nCHAR(13) + nCHAR(10)) within104    GROUP(105  ORDER BY bu.ElementIndex)106  FROM @ElementBuffer bu107  RETURN(@erg)108END
Open raw exported source
SQL ยท Raw17 lines
1     create   function dbo.QBM_FSQIndexDef_i (@TableOrTypeName nvarchar(64), @IndexPattern nvarchar(64) , @IncludeGenerated bit = 0 ) returns nvarchar2(max) as begin declare @erg nvarchar(max) = '' declare @ElementBuffer  table (ElementIndex int identity , ContentFull nvarchar(max) collate database_default3 ) declare @TableType varchar(16) = 'TAB' if exists (select top 1 1 from sys.tables t with (readpast) where t.name = @TableOrTypeName and t.is_memory_optimized4 = 1 ) begin select @TableType = 'MEMTAB' end if exists (select top 1 1 from sys.table_types t with (readpast) where t.name = @TableOrTypeName ) begin 5select @TableType = 'TYPE' end  if @TableType in ('MEMTAB', 'TYPE') begin insert into @ElementBuffer(ContentFull) select '	, index ' + i.name + case when6 h.bucket_count is null then '' else ' hash' end + ' (' + dbo.QBM_FSQIndexColumns(@TableOrTypeName, i.name, 0) + ')' + case when h.bucket_count is null7 then '' else ' with (BUCKET_COUNT = '+ ltrim(STR(h.bucket_count)) +')' end from ( select t1.object_id from sys.tables t1 with (readpast) where t1.name8 = @TableOrTypeName union all select t2.type_table_object_id from sys.table_types t2 with (readpast) where t2.name = @TableOrTypeName ) as t join sys.indexes9 i with (readpast) on t.object_id = i.object_id and i.is_primary_key = 0  and i.is_hypothetical = 0  and i.type not in (3,4,5,6) left outer join sys.hash_indexes10 h with (readpast) on h.index_id = i.index_id and h.object_id = i.object_id where i.type_desc <> 'HEAP' and i.name like @IndexPattern order by i.name end11   if @TableType = 'TAB' begin insert into @ElementBuffer(ContentFull) select 'exec QBM_PIndexCreate ''' + @TableOrTypeName + ''', ''' + i.name + ''', '12 + convert(varchar(16), i.is_unique) + ', ''' + dbo.QBM_FSQIndexColumns(@TableOrTypeName, i.name, 0) + ''' ' + case when dbo.QBM_FSQIndexColumns(@TableOrTypeName13, i.name, 1) > ' ' then ', ''' + dbo.QBM_FSQIndexColumns(@TableOrTypeName, i.name, 1) + ''' ' else '' end from sys.tables t with (readpast) join sys.indexes14 i with (readpast) on t.object_id = i.object_id and t.name = @TableOrTypeName and i.name like @IndexPattern where i.is_primary_key = 0  and i.is_hypothetical15 = 0 and (i.name not like 'GEN[_]%' or @IncludeGenerated = 1 )  and i.type not in (3,4,5,6) end  select @erg = string_agg(bu.ContentFull , nCHAR(13) + 16nCHAR(10) )  within group(order by bu.ElementIndex) from @ElementBuffer bu return(@erg) end 17