how to copy the index?
-
I would like to add the index from one table on to another table that would be used as back up during the processes. I copied the structure using the select into, but it does not copy the indexes. how could i do it?
-
Script the index and the fire against the destination table before that remember to change the tablename in the script generated.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com -
i got some information from the systables, but i got blocked on the alter table
-- set the index
DECLARE
@l_n_id INT,
@l_a_table_name
varchar (70)
@l_a_table_name_index
varchar (70)
@l_a_object_id_index
varchar (70)
@l_a_object_Name_index
varchar (70)
@l_a_object_Name_ID_index
varchar (70)
@l_a_object_Name_Type
varchar (70)
DECLARE
Lee_index CURSOR FOR
Select
a.name,a.object_id,b.name, b.index_id,b.type
FROM
sys.objects a
inner
join sys.indexes b
on
a.object_id = b.object_id
inner
join sys.columns c
on
c.object_id = b.object_id
where
a.name = @c_a_tabla
OPEN
Lee_index
FETCH
NEXT FROM Lee_index
INTO
@l_a_table_name_index,@l_a_object_id_index,@l_a_object_Name_index,@l_a_object_Name_ID_index,@l_a_object_Name_Type
WHILE
@@FETCH_STATUS = 0
BEGIN
set
@l_a_table_name = 'TMP_PRV_'+ @c_a_tabla
set
@Index_name = 'alter table '+ @l_a_table_name
EXEC
(@Index_name)
End
FETCH
NEXT FROM Lee_index
INTO
@l_a_table_name_index,@l_a_object_id_index,@l_a_object_Name_index,@l_a_object_Name_ID_index,@l_a_object_Name_Type
END
CLOSE
Lee_index
DEALLOCATE
Lee_index
-
Even with this script you should be changing the name of the table. Just do that before you run the script.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com -
Thank you for your answer
I changed the name of the destination table, if the original table is called Products, i called the new Un-indexed one to TMP_PRV_Products, i set the name of the tables on the variables @c_a_tabla (original table) and @l_a_table_name as the destination un-indexed table.
I am stuck on the alter, how to insert the Index dinamically depending on the table. i was thinking of a cursr, but still stuck...
i would apreciate some help.
-
What about my older suggestion. Script the index from the already existing table nad changing the table name and using it again in the destination table.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply