dbo.QBM_FTIndexesForColumns

1create function dbo.QBM_FTIndexesForColumns ( @TableName varchar(30)   , @Columns varchar(max)    , @Includes varchar(max)  ) returns @erg table (IndexName
2 varchar(256) collate database_default  , IsPK bit , IsUnique bit , HasMoreColumns bit , HasAllIncludes bit , HasMoreIncludes bit ) as begin declare @ColumnsList
3 QBM_YParameterList   declare @IncludesList QBM_YParameterList  declare @CountColumns int declare @CountIncludes int insert into @ColumnsList(Parameter1
4, Parameter2) select trim(l.ParameterValue), dbo.QBM_FCVStringPadLeft( dbo.QBM_FCVIntToString(l.OrderNumber), 3, '0') from dbo.QBM_FCVStringToList(@columns
5, ',', 1, 0) l select @CountColumns = @@ROWCOUNT  update @ColumnsList set Parameter3 = 'desc' ,Parameter1 = trim(left(Parameter1, patindex('% desc%', Parameter1
6))) from @ColumnsList l where l.Parameter1 like '% desc%' insert into @IncludesList(Parameter1) select l.ParameterValue from dbo.QBM_FCVStringToList(@Includes
7, ',', 1, 0) l select @CountIncludes = @@ROWCOUNT   insert into @erg(IndexName, IsPK, IsUnique, HasMoreColumns, HasAllIncludes, HasMoreIncludes) select
8 ali.indexname , ali.IsPK, ali.IsUnique  , mai.HasMoreColumns , case when @CountIncludes > 0 then sign(len(isnull(inx.indexname, ''))) else 1 end as HasAllIncludes
9 , 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.objects
10 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 not
11 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_FCVStringPadLeft
12( 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_columns
13 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.indexes
14 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.Parameter1
16, cl.Parameter3 ) = ec.columnname group by ec.indexname having count(distinct cl.Parameter1) = @CountColumns ) as mai  on ali.indexname = mai.indexname
17 left outer join ( select ei.indexname , sign(count(*) - @CountIncludes) as HasMoreIncludes from (select i.name as indexname, c.name as columnname from
18 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_column
19 = 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,5
20,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.Parameter1
21 = ei.columnname group by ei.indexname having count(distinct il.Parameter1) >= @CountIncludes ) as inx  on mai.indexname = inx.indexname return end 
22