i want to create the sp to recreate the indexes of all the table. here
is what i need:
CREATE UNIQUE CLUSTERED INDEX
[table_name$32$0_idx] ON
[dbo].[table_name$32$0] ([bucket], [f2], [f5402], [f47], [f8])
WITH DROP_EXISTING
in order to get all the <table name> (columns)
i use sp_pkeys:
exec sp_pkeys [table_name$32$0]
here is the table where i insertet sp_pkeys data.
create table keys (
table_qualifier sysname,
table_owner sysname,
table_name sysname,
column_name sysname,
key_seq smallint,
pk_name sysname
)
i think i need to create a temp table and insert the data into it and
then fetch all i need into cursor ant exec CREATE INDEX WITH
DROP_EXISTING statment, but
i don't know how to convert column into a row (as sp_pkeys returns all
the index's keys into 1 column) .
thanxHi,
You could use DBCC DBREINDEX command to Reindex all indexes.
Use the below script to reindex all tables in a database:-
EXEC sp_MSforeachtable @.command1 = 'DBCC DBREINDEX ("?")'
Thanks
HARI
SQL Server MVP
"benamis" <nera@.meilo.lt> wrote in message
news:urRVtnFnFHA.3316@.TK2MSFTNGP14.phx.gbl...
> hi
> i want to create the sp to recreate the indexes of all the table. here is
> what i need:
> CREATE UNIQUE CLUSTERED INDEX
> [table_name$32$0_idx] ON
> [dbo].[table_name$32$0] ([bucket], [f2], [f5402], [f47], [f8])
> WITH DROP_EXISTING
> in order to get all the <table name> (columns)
> i use sp_pkeys:
> exec sp_pkeys [table_name$32$0]
> here is the table where i insertet sp_pkeys data.
> create table keys (
> table_qualifier sysname,
> table_owner sysname,
> table_name sysname,
> column_name sysname,
> key_seq smallint,
> pk_name sysname
> )
> i think i need to create a temp table and insert the data into it and
> then fetch all i need into cursor ant exec CREATE INDEX WITH DROP_EXISTING
> statment, but
> i don't know how to convert column into a row (as sp_pkeys returns all the
> index's keys into 1 column) .
> thanx|||Maybe this might help:
http://milambda.blogspot.com/2005/0...in-current.html
ML|||yes i know about DBCC DBREINDEX , but CREATE INDEX WITH DROP_EXISTING
is more efective [~2x times] (at least for navision db).
Hari Pra
wrote:> Hi,
> You could use DBCC DBREINDEX command to Reindex all indexes.
> Use the below script to reindex all tables in a database:-
> EXEC sp_MSforeachtable @.command1 = 'DBCC DBREINDEX ("?")'
> Thanks
> HARI
> SQL Server MVP
> "benamis" <nera@.meilo.lt> wrote in message
> news:urRVtnFnFHA.3316@.TK2MSFTNGP14.phx.gbl...
>
>
>|||If you want to rebuild indexes without using the DBCC command, you can scrip
t
your the indexes and maybe create a job using this script.
You can script any object easily in the Enterprise Manager.
ML
