October 17, 2008 at 8:56 am
I am looking for a way by which the indexes for a table can be scripted out(automated), automate the drop , and a subsequent automated re-create of the same . Please give ur suggestions for the same.
October 17, 2008 at 10:08 am
These are options you can set in scripting options. Tools, Options, Scripting. Towards the bottom of the list is something mentioning "include index creation".
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 17, 2008 at 10:21 am
The issue is I need to setup a job, which scripts out the indexes of a few tables. What I am looking for is a script or a hint for this to be done.
October 17, 2008 at 11:48 am
If you are in 2005 DISABLE/ENABLE does that for you ... if what you are looking for is to drop/load/rebuild 😉
* Noel
October 17, 2008 at 12:24 pm
declare
@schema sysname,
@table sysname,
@index sysname,
@stmt varchar(max)
declare indexes cursor
for
select
s.name,
t.name,
i.name
from
sys.schemas s
join sys.tables t on t.schema_id = s.schema_id
join sys.indexes i on t.object_id = i.object_id
where
s.name = 'piza'
and t.name = 'prospect'
open indexes
fetch indexes
into @schema,
@table,
while @@fetch_status<>(-1)
begin
set @stmt = 'drop index ' + @schema + '.' + @table + '.' + @index
print @stmt
fetch indexes
into @schema,
@table,
end
close indexes
deallocate indexes
--------------------------------------------------------------------
declare
@object_id int,
@index_id tinyint,
@schema_name sysname,
@table_name sysname,
@index_name sysname,
@type tinyint,
@uniqueness bit,
@indexed_column sysname,
@included_column sysname,
@indexed_columns varchar(max),
@included_columns varchar(max),
@has_included_cols bit,
@is_descending_key bit,
@stmt varchar(max),
@crlf char(2)
set @crlf = char(13) + char(10)
declare indexes cursor
for
select
schema_name = s.name,
table_name = t.name,
index_id = i.index_id,
index_name = i.name,
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
order
by s.name,
t.name,
i.index_id
open indexes
fetch
indexes
into
@schema_name,
@table_name ,
@index_id ,
@index_name ,
@type ,
@uniqueness
while @@fetch_status<>(-1)
begin
select @object_id = object_id(@schema_name + '.' + @table_name)
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) + ')'
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
set @stmt =
'create ' +
case @uniqueness when 1 then 'unique ' else '' end +
case @type when 1 then 'clustered ' else '' end +
'index ' + @index_name + @crlf +
'on ' + @schema_name + '.' + @table_name + @indexed_columns + @crlf +
isnull(@included_columns,'') +
'g' + 'o' + @crlf + @crlf
print @stmt
fetch
indexes
into
@schema_name,
@table_name ,
@index_id ,
@index_name ,
@type ,
@uniqueness
end
close indexes
deallocate indexes
October 20, 2008 at 3:23 am
Thanks you so much for the reply :-).
More suggestions are welcome 🙂
October 22, 2008 at 6:27 am
Can someone please give a similiar suggestion for SQL 2000.
October 22, 2008 at 3:03 pm
Since this is 2005 forum, I assumed you're asking for 2005. For 2000 it would much easier - it did not have covered indexes. You can go through this script and eliminate everything that's related to that. I actually have written this script by updating 2000, but now I checked my code library and unfortunately I could not find it.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply