Back to OIM Explorer

dbo.QBM_FTIndexesForColumns

Table FunctionSQL_TABLE_VALUED_FUNCTIONSandbox DB

Table Function.

Source: sandbox-db sys.sql_modules

Source size: 3.296 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_FCVIntToString source text reference
references source dbo.QBM_FCVStringPadLeft source text reference

References

Referenced By

dbo.QBM_PIndexCreate

Complete Source

1CREATE FUNCTION dbo.QBM_FTIndexesForColumns(2  @TableName varchar(30),3  @Columns varchar(max),4  @Includes varchar(max)5) RETURNS @erg TABLE(IndexName varchar(256) collate database_default,6IsPK BIT,7IsUnique BIT,8HasMoreColumns BIT,9HasAllIncludes BIT,10HasMoreIncludes BIT11)12AS13BEGIN14  DECLARE @ColumnsList QBM_YParameterList15  DECLARE @IncludesList QBM_YParameterList16  DECLARE @CountColumns int17  DECLARE @CountIncludes int18  INSERT INTO @ColumnsList(Parameter1,19  Parameter2)20  SELECT21    trim(l.ParameterValue),22    dbo.QBM_FCVStringPadLeft(dbo.QBM_FCVIntToString(l.OrderNumber),23    3,24    '0')25  FROM dbo.QBM_FCVStringToList(@columns,26  ',',27  1,28  0) l29  SELECT @CountColumns = @@ROWCOUNT30  UPDATE @ColumnsList31  SET Parameter3 = 'desc',32  Parameter1 = trim(33  LEFT(Parameter1, patindex('% desc%', Parameter1)))34  FROM @ColumnsList l35  WHERE36    l.Parameter1 LIKE '% desc%'37  INSERT INTO @IncludesList(Parameter1)38  SELECT l.ParameterValue39  FROM dbo.QBM_FCVStringToList(@Includes,40  ',',41  1,42  0) l43  SELECT @CountIncludes = @@ROWCOUNT44  INSERT INTO @erg(IndexName,45  IsPK,46  IsUnique,47  HasMoreColumns,48  HasAllIncludes,49  HasMoreIncludes)50  SELECT51    ali.indexname,52    ali.IsPK,53    ali.IsUnique,54    mai.HasMoreColumns,55  CASE56    WHEN @CountIncludes > 0 THEN57  sign(len(isnull(inx.indexname, '')))58  ELSE 159  END AS HasAllIncludes,60  isnull(inx.HasMoreIncludes,61  0) AS HasMoreIncludes62  FROM(63  SELECT64    i.name AS indexname,65    i.is_primary_key AS IsPK,66    i.is_unique AS IsUnique67  FROM sys.objects t68    WITH(readpast)69  JOIN sys.indexes i70    WITH(readpast)71    ON i.object_id = t.object_id72  WHERE73    t.name = @TableName AND i.is_hypothetical = 0 AND i.type NOT IN(3, 4, 5, 6)) AS ali74  JOIN(75  SELECT76    ec.indexname,77    sign(count(*) - @CountColumns) AS HasMoreColumns78  FROM(79  SELECT80    i.name AS indexname, CONCAT(dbo.QBM_FCVStringPadLeft(dbo.QBM_FCVIntToString(ic.key_ordinal), 3,81    '0'), c.name, CASE ic.is_descending_key82    WHEN 1 THEN83    'desc'84  ELSE ''85  END) AS columnname86  FROM sys.index_columns ic87    WITH(readpast)88  JOIN sys.objects t89    WITH(readpast)90    ON t.object_id = ic.object_id AND t.name = @TableName AND ic.is_included_column = 091  JOIN sys.indexes i92    WITH(readpast)93    ON ic.index_id = i.index_id AND i.object_id = t.object_id AND i.is_hypothetical = 0 AND i.type NOT IN(3,94  4, 5, 6)95  JOIN sys.columns c96    WITH(readpast)97    ON t.object_id = c.Object_id AND c.column_id = ic.column_id) ec98  LEFT99  OUTER100  JOIN @ColumnsList cl101    ON CONCAT(cl.Parameter2, cl.Parameter1, cl.Parameter3) = ec.columnname102  GROUP BY ec.indexname103  HAVING count(DISTINCT cl.Parameter1) = @CountColumns) AS mai104    ON ali.indexname = mai.indexname105  LEFT106  OUTER107  JOIN(108  SELECT109    ei.indexname,110    sign(count(*) - @CountIncludes) AS HasMoreIncludes111  FROM(112  SELECT113    i.name AS indexname, c.name AS columnname114  FROM sys.index_columns ic115    WITH(readpast)116  JOIN sys.objects t117    WITH(readpast)118    ON t.object_id = ic.object_id AND t.name = @TableName AND ic.is_included_column = 1119  JOIN sys.indexes i120    WITH(readpast)121    ON ic.index_id = i.index_id AND i.object_id = t.object_id AND i.is_hypothetical = 0 AND i.type NOT IN(3,122  4, 5, 6)123  JOIN sys.columns c124    WITH(readpast)125    ON t.object_id = c.Object_id AND c.column_id = ic.column_id) AS ei126  LEFT127  OUTER128  JOIN @IncludesList il129    ON il.Parameter1 = ei.columnname130  GROUP BY ei.indexname131  HAVING count(DISTINCT il.Parameter1) >= @CountIncludes) AS inx132    ON mai.indexname = inx.indexname133  RETURN134END

Open raw exported source

1create function dbo.QBM_FTIndexesForColumns ( @TableName varchar(30)   , @Columns varchar(max)    , @Includes varchar(max)  ) returns @erg table (IndexName2 varchar(256) collate database_default  , IsPK bit , IsUnique bit , HasMoreColumns bit , HasAllIncludes bit , HasMoreIncludes bit ) as begin declare @ColumnsList3 QBM_YParameterList   declare @IncludesList QBM_YParameterList  declare @CountColumns int declare @CountIncludes int insert into @ColumnsList(Parameter14, Parameter2) select trim(l.ParameterValue), dbo.QBM_FCVStringPadLeft( dbo.QBM_FCVIntToString(l.OrderNumber), 3, '0') from dbo.QBM_FCVStringToList(@columns5, ',', 1, 0) l select @CountColumns = @@ROWCOUNT  update @ColumnsList set Parameter3 = 'desc' ,Parameter1 = trim(left(Parameter1, patindex('% desc%', Parameter16))) from @ColumnsList l where l.Parameter1 like '% desc%' insert into @IncludesList(Parameter1) select l.ParameterValue from dbo.QBM_FCVStringToList(@Includes7, ',', 1, 0) l select @CountIncludes = @@ROWCOUNT   insert into @erg(IndexName, IsPK, IsUnique, HasMoreColumns, HasAllIncludes, HasMoreIncludes) select8 ali.indexname , ali.IsPK, ali.IsUnique  , mai.HasMoreColumns , case when @CountIncludes > 0 then sign(len(isnull(inx.indexname, ''))) else 1 end as HasAllIncludes9 , isnull(inx.HasMoreIncludes, 0) as HasMoreIncludes from ( select i.name as indexname, i.is_primary_key as IsPK, i.is_unique as IsUnique from sys.objects10 t with (readpast) join sys.indexes i with (readpast) on i.object_id = t.object_id where t.name = @TableName  and i.is_hypothetical = 0  and i.type not11 in (3,4,5,6)  ) as ali  join ( select ec.indexname , sign(count(*) - @CountColumns) as HasMoreColumns  from (select i.name as indexname, concat(dbo.QBM_FCVStringPadLeft12( dbo.QBM_FCVIntToString( ic.key_ordinal), 3, '0') , c.name, case ic.is_descending_key when 1 then 'desc' else '' end ) as columnname from sys.index_columns13 ic with (readpast) join sys.objects t with (readpast) on t.object_id = ic.object_id and t.name = @TableName and ic.is_included_column = 0 join sys.indexes14 i with (readpast) on ic.index_id = i.index_id and i.object_id = t.object_id  and i.is_hypothetical = 0   and i.type not in (3,4,5,6) join sys.columns 15c with (readpast) on t.object_id = c.Object_id and c.column_id = ic.column_id ) ec left outer join @ColumnsList cl on concat(cl.Parameter2 , cl.Parameter116, cl.Parameter3 ) = ec.columnname group by ec.indexname having count(distinct cl.Parameter1) = @CountColumns ) as mai  on ali.indexname = mai.indexname17 left outer join ( select ei.indexname , sign(count(*) - @CountIncludes) as HasMoreIncludes from (select i.name as indexname, c.name as columnname from18 sys.index_columns ic with (readpast) join sys.objects t with (readpast) on t.object_id = ic.object_id and t.name = @TableName and ic.is_included_column19 = 1 join sys.indexes i with (readpast) on ic.index_id = i.index_id and i.object_id = t.object_id  and i.is_hypothetical = 0   and i.type not in (3,4,520,6) join sys.columns c with (readpast) on t.object_id = c.Object_id and c.column_id = ic.column_id ) as ei left outer join @IncludesList il on il.Parameter121 = ei.columnname group by ei.indexname having count(distinct il.Parameter1) >= @CountIncludes ) as inx  on mai.indexname = inx.indexname return end 22