Script for reindexing

Dear all,
Is there any script available to reindex/ re-create index in a given
database.
Important is that script should first create clustered indexes and then non
clustered indexes as recommended by Microsoft.
Regards,
Kay
You can generate a SQL script (ensuring to select both DROP and ADD
syntax when setting up the scripting) and then run this to
drop/re-create indexes.
ALI
Kay wrote:
> Dear all,
> Is there any script available to reindex/ re-create index in a given
> database.
> Important is that script should first create clustered indexes and then non
> clustered indexes as recommended by Microsoft.
> Regards,
> Kay
|||Run the following script in Query Analyzer and set the result as Text to
generate a script that reindexes all the indexes in the right order:
SELECT 'SELECT ''Reindexing index ' + name + ' on table ' + OBJECT_NAME (id)
+ ''''+ CHAR(13) +
'DBCC DBREINDEX(0, ''' + OBJECT_NAME (id) + ''', ''' + name + ''')'
-- + CHAR(13) +
-- 'DBCC SHRINKFILE(1, TRUNCATEONLY)'
FROM sysindexes
WHERE indid BETWEEN 1 AND 254
AND OBJECTPROPERTY(id, 'IsMSShipped') = 0
AND INDEXPROPERTY(id, name, 'IsStatistics') = 0
ORDER BY OBJECT_NAME(id), indid
Jacco Schalkwijk
SQL Server MVP
"Kay" <CallDBA@.hotmail.com> wrote in message
news:%23qXB4f62FHA.3000@.TK2MSFTNGP12.phx.gbl...
> Dear all,
> Is there any script available to reindex/ re-create index in a given
> database.
> Important is that script should first create clustered indexes and then
> non clustered indexes as recommended by Microsoft.
> Regards,
> Kay
>
>
|||Books Online, DBCC SHOWCONTIG. Here you find a script that only defrag if frag level is above a
certain threshold. It is easy to change the script to execute DBCC BREINDEX instead of INDEXDEFRAG.
See http://www.microsoft.com/technet/pro.../ss2kidbp.mspx for more
information.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid > wrote in message
news:e%23W1yy62FHA.2676@.TK2MSFTNGP15.phx.gbl...
> Run the following script in Query Analyzer and set the result as Text to generate a script that
> reindexes all the indexes in the right order:
> SELECT 'SELECT ''Reindexing index ' + name + ' on table ' + OBJECT_NAME (id)
> + ''''+ CHAR(13) +
> 'DBCC DBREINDEX(0, ''' + OBJECT_NAME (id) + ''', ''' + name + ''')'
> -- + CHAR(13) +
> -- 'DBCC SHRINKFILE(1, TRUNCATEONLY)'
> FROM sysindexes
> WHERE indid BETWEEN 1 AND 254
> AND OBJECTPROPERTY(id, 'IsMSShipped') = 0
> AND INDEXPROPERTY(id, name, 'IsStatistics') = 0
> ORDER BY OBJECT_NAME(id), indid
>
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Kay" <CallDBA@.hotmail.com> wrote in message news:%23qXB4f62FHA.3000@.TK2MSFTNGP12.phx.gbl...
>