August 15, 2024 at 4:46 pm
test
August 15, 2024 at 4:48 pm
if OBJECT_ID('UTIL.index_definition','fn')is not null
drop function UTIL.index_definition
go
create function UTIL.index_definition
(
@schema sysname,
@table sysname,
@index sysname,
@include_on_storage bit = 1
)
returns varchar(max)
/*---------------------------------------------------------------------------------
select UTIL.index_definition
('dw', 'FactCompareResult','FCRBCKIdx',1 )
select UTIL.index_definition
('dw', 'FactCompareResult','FCRBCKIdx',0 )
Dynamic invocation:
set nocount on
print 'set nocount on'
select 'select UTIL.index_definition(''' +
s.name + ''',''' + t.name + ''',''' + i.name + ''',1)'
from sys.schemas s
join sys.tables t on s.schema_id = t.schema_id
join sys.indexes i on t.object_id = i.object_id
where i.name is not null
----------------------------------------------------------------------------------*/
as
begin
declare
@object_id int,
@index_id tinyint,
@type tinyint,
@uniqueness bit,
@indexed_column sysname,
@included_column sysname,
@indexed_columns varchar(max),
@included_columns varchar(max),
@is_descending_key bit,
@storage_type char(2),
@storage_name sysname,
@on_storage varchar(max),
@msg varchar(200),
@stmt varchar(max),
@crlf char(2)
set @crlf = char(13) + char(10)
----- 1: ----- General data: -----
select @object_id = t.object_id,
@index_id = i.index_id,
@type = i.type,
@uniqueness = i.is_unique
from sys.schemas s
join sys.tables t on s.schema_id = t.schema_id
join sys.indexes i on t.object_id = i.object_id
where i.type > 0 -- none-heap
and s.name = @schema
and t.name = @table
and i.name = @index
if @object_id is null or @index_id is null begin
set @msg = 'Index ' + @index + ' does not exist on table ' + @schema + '.' + @table
--raiserror (@msg , 12,1)
return @msg
end
----- 2: ----- Indexed columns: -----
set @indexed_columns = '('
declare indexed_columns cursor
for
select c.name,
ic.is_descending_key
from sys.index_columns ic join sys.columns c
on ic.column_id = c.column_id
and ic.object_id = c.object_id
where ic.object_id = @object_id
and ic.index_id = @index_id
and ic.is_included_column = 0
order by ic.index_column_id
open indexed_columns
fetch indexed_columns
into @indexed_column, @is_descending_key
while @@fetch_status<>(-1)
begin
set @indexed_columns = @indexed_columns + @indexed_column +
case @is_descending_key when 1 then ' desc ' else '' end + ', '
fetch indexed_columns
into @indexed_column, @is_descending_key
end
close indexed_columns
deallocate indexed_columns
set @indexed_columns = left(@indexed_columns, len(@indexed_columns)-1) + ')'
----- 3: ----- Included columns: -----
if exists
(select object_id
from sys.index_columns
where object_id = @object_id
and index_id = @index_id
and is_included_column = 1 ) begin
set @included_columns = 'include ('
declare included_columns cursor
for
select c.name,
ic.is_descending_key
from sys.index_columns ic join sys.columns c
on ic.column_id = c.column_id
and ic.object_id = c.object_id
where ic.object_id = @object_id
and ic.index_id = @index_id
and ic.is_included_column = 1
order by ic.index_column_id
open included_columns
fetch included_columns
into @included_column, @is_descending_key
while @@fetch_status<>(-1)
begin
set @included_columns = @included_columns + @included_column +
case @is_descending_key when 1 then ' desc ' else '' end + ', '
fetch included_columns
into @included_column, @is_descending_key
end
close included_columns
deallocate included_columns
set @included_columns = left(@included_columns, len(@included_columns)-1) + ')' + @crlf
end
----- 4: ----- ON storage clause: -----
if @include_on_storage = 1 begin
select @storage_type = ds.type,
@storage_name = ds.name
from sys.indexes i join sys.data_spaces ds
on i.data_space_id = ds.data_space_id
where i.object_id = @object_id
and i.index_id = @index_id
set @on_storage = 'on ' +
case @storage_name
when 'PRIMARY' then '[' + @storage_name + ']'
else @storage_name
end +
case @storage_type
when 'PS' then @indexed_columns
when 'FG' then ''
end
end
----- 5: ----- Final statement: -----
set @stmt =
'create ' +
case @uniqueness when 1 then 'unique ' else '' end +
case @type when 1 then 'clustered ' else '' end +
'index ' + @index + @crlf +
'on ' + @schema + '.' + @table + @indexed_columns + @crlf +
isnull(@included_columns,'') +
isnull(@on_storage,'') + @crlf
return @stmt
end
August 15, 2024 at 6:46 pm
should this be deleted? Not sure what is happening here.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply