dbo.QBM_PIndexCreateGenerated_XA

1  create   procedure QBM_PIndexCreateGenerated_XA (@TableName varchar(30) ) as begin declare @ColumnName varchar(30) declare @Indexname varchar(30
2) declare @ColumnList nvarchar(max) declare @IncludeList nvarchar(max) declare @IsInclude bit declare @UID_QBMUniqueGroup varchar(38) declare @UID_QBMUniqueGroup_alt
3 varchar(38)  declare @ElementBufferMulti QBM_YCursorBuffer declare @ElementCount int declare @ElementIndex int declare @DebugSwitch int = 0 SET XACT_ABORT
4 OFF BEGIN TRY select @UID_QBMUniqueGroup_alt = '#' select @ColumnList = '' select @IncludeList = ''  insert into @ElementBufferMulti (UID1, Ident2, Bit1
5) select x.UID_QBMUniqueGroup, x.ColumnName, x.IsInclude from (  select ug.UID_QBMUniqueGroup, tc.TableName, c.ColumnName, convert(int, c.IsPKMember) +
6 convert(int, c.IsForeignKey) as KeyMember ,case when c.SchemaDataLen > 800 then 1 else 0 end as IsInclude from QBMUniqueGroup ug with (readpast) join 
7QBMUniqueGroupHasColumn ghc with (readpast) on ug.UID_QBMUniqueGroup = ghc.UID_QBMUniqueGroup join DialogTable tc with (readpast) on tc.UID_DialogTable
8 = ug.UID_DialogTable join DialogColumn c with (readpast) on c.XObjectKey = ghc.ObjectKeyDialogColumn and tc.TableType in ('B', 'T') and c.SchemaDataLen
9 <> -1  where tc.TableName = @TableName union  select ug.UID_QBMUniqueGroup, tb.TableName, cb.ColumnName, convert(int, cb.IsPKMember) + convert(int, cb.IsForeignKey
10) as KeyMemeber ,case when c.SchemaDataLen > 800 then 1 else 0 end as IsInclude from QBMUniqueGroup ug with (readpast) join QBMUniqueGroupHasColumn ghc
11 with (readpast) on ug.UID_QBMUniqueGroup = ghc.UID_QBMUniqueGroup join DialogTable tc with (readpast) on tc.UID_DialogTable = ug.UID_DialogTable join 
12DialogColumn c with (readpast) on c.XObjectKey = ghc.ObjectKeyDialogColumn and tc.TableType in ('V') join DialogTable tb with (readpast) on tc.UID_DialogTableBase
13 = tb.UID_DialogTable join DialogColumn cb with (readpast) on c.UID_BaseColumn = cb.UID_DialogColumn and cb.SchemaDataLen <> -1  where tb.TableName = @TableName
14 union  select tc.UID_DialogTable, tc.TableName, c.ColumnName, convert(int, c.IsPKMember) + convert(int, c.IsForeignKey) as KeyMemeber ,case when c.SchemaDataLen
15 > 800 then 1 else 0 end as IsInclude from DialogTable tc with (readpast) join DialogColumn c with (readpast) on c.UID_DialogTable = tc.UID_DialogTable
16 and tc.TableType in ('B', 'T') and c.IsAlternatePKMember = 1 and c.SchemaDataLen <> -1  where tc.TableName = @TableName ) as x  join sys.tables ts with
17 (nolock) on x.TableName = ts.name collate database_default and ts.type = 'U'  join sys.schemas s with (nolock) on ts.schema_id = s.schema_id and s.name
18 = 'dbo' join sys.columns cs with (nolock) on ts.object_id = cs.object_id and cs.name = x.ColumnName collate database_default where not exists (Select 
19top 1 1 from QBM_VHeavyLoadTables h where h.TableName = x.TableName and h.IsNoAutoIndex = 1 ) order by x.UID_QBMUniqueGroup, x.TableName, x.KeyMember desc
20 , x.ColumnName select @ElementCount = @@ROWCOUNT select @ElementIndex = 1 while @ElementIndex <= @ElementCount begin select top 1 @UID_QBMUniqueGroup 
21= bu.UID1 , @columnname = bu.Ident2 , @IsInclude = bu.Bit1 from @ElementBufferMulti bu where bu.ElementIndex = @ElementIndex if @DebugSwitch > 0 begin 
22print @UID_QBMUniqueGroup + '.' + @TableName + '.' + @columnname + '  ' + @ColumnList + '  ' + @IncludeList + str(@IsInclude) end if @UID_QBMUniqueGroup
23 <> @UID_QBMUniqueGroup_alt begin if @UID_QBMUniqueGroup_alt <> '#' begin select @Indexname = dbo.QBM_FGICodeName('XA', 'uniqueGroup' + @UID_QBMUniqueGroup_alt
24 + @Tablename + @ColumnList + @IncludeList) if @IncludeList > ' ' begin select @IncludeList = substring(@IncludeList, 2, len(@IncludeList) -1 ) end if 
25@ColumnList > ' ' begin select @ColumnList = substring(@ColumnList, 2, len(@ColumnList) -1 ) exec QBM_PIndexCreate @Tablename, @Indexname, 0, @ColumnList
26, @IncludeList end select @ColumnList = '' select @IncludeList = '' end  select @UID_QBMUniqueGroup_alt = @UID_QBMUniqueGroup end if @IsInclude = 0 begin
27 select @ColumnList = @ColumnList + ', ' + @ColumnName end else begin select @IncludeList = @IncludeList + ', ' + @ColumnName end select @ElementIndex 
28+= 1 end  if @ColumnList > ' ' begin select @Indexname = dbo.QBM_FGICodeName('XA', 'uniqueGroup' + @UID_QBMUniqueGroup_alt + @Tablename + @ColumnList +
29 @IncludeList) if @IncludeList > ' ' begin select @IncludeList = substring(@IncludeList, 2, len(@IncludeList) -1 ) end if @ColumnList > ' ' begin select
30 @ColumnList = substring(@ColumnList, 2, len(@ColumnList) -1 ) exec QBM_PIndexCreate @Tablename, @Indexname, 0, @ColumnList, @IncludeList end end END TRY
31 BEGIN CATCH exec QBM_PSessionErrorAdd default declare @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow() RAISERROR (@Rethrow, 18, 1) WITH NOWAIT
32 END CATCH ende: return end 
33