rewrite index scripts - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

rewrite index scripts

Author

Topic

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-05-29 : 21:02:19

Hello all

We are load testing SQL 2005 and I need to re-write the index scripts that we have from 2000. Is there an easier way to rewrite the scripts without having to go to each job, then each step and modify it?

Our current index script template is:


Create NonClustered Index [IdxABCDE] 
	On dbo.blahblah(blahID) 
	With FillFactor = 90, Statistics_NoRecompute
	On [Index2]
Go

and I need to rewrite it as:


ALTER INDEX [IdxABCDE] ON [dbo].[blahblah]
REBUILD WITH (FILLFACTOR = 90, ONLINE = OFF,SORT_IN_TEMPDB=ON, STATISTICS_NORECOMPUTE = ON, MAXDOP=4)

I am thinking I would have to rewrite the scripts from scratch. We have hundreds of index scripts. So before I brace myself to lot of typing, just wanted to find out if there is any easier way..

Thanks..

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-05-29 : 22:43:02

something like this maybe:


select 'ALTER INDEX ' + quotename(name) +' ON [dbo].' + quotename(object_name(object_id)) + '
REBUILD WITH (FILLFACTOR = 90, ONLINE = OFF,SORT_IN_TEMPDB=ON, STATISTICS_NORECOMPUTE = ON, MAXDOP=4)'
from sys.indexes

www.elsasoft.org

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-05-30 : 11:43:59

Hi Jesse

I did think of doing that but, the thing is not all indexes in sysindexes are covered in the maintenance jobs. Basically the maintenance jobs involving index creation have to be rewritten with the new syntax.

thanks..

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-05-30 : 12:00:26

in that case, why not just dump the names of the indexes that ARE covered in maint jobs into a table, and then join them with sys.indexes in the script above?

www.elsasoft.org

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-05-30 : 19:28:54

I am trying to get the list of indexes covered in the jobs via query as:


SELECT *
FROM msdb..sysjobsteps
WHERE command LIKE 'create %index%'

Was that how you meant I should get the indexes that are covered?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources