You can either use the syntax provided above for SQL Server 2000 or: Rebuild all indexes with specifying the fill factorĮXEC sp_MSforeachtable '?' DBCC DBREINDEX ('?', ' ', )" Rebuild all indexes with keeping the default fill factor for each indexĮXEC sp_MSforeachtable '?' DBCC DBREINDEX ('?')"
HOW TO DEFRAG SQL 2012 EXPRESS DATABASE HOW TO
Hereunder I propose the syntax on how to rebuild all the indexes of a given database by utilizing the sp_MSforeachtable stored procedure. There is not need to worry :) In SQL Server there is the undocumented stored procedure sp_MSforeachtable which allows for recursively executing a T-SQL statement (or more) for all the tables within a database with the use of a single line of code. Though, I know that workarounds are not very desirable in many cases as they might demand a large amount of time :) A workaround is to run different rebuild statements for each table. Under normal circumstances there is not a direct way allowing to rebuild all the indexes of a database with a single command. My previous post on the topic explained ways of rebuilding specific or all the indexes within a table. To this end, in the worst scenario, the DBA will need to rebuild the indexes in all the tables of the database. It is a fact that in some cases where a large amount of indexes in a database on SQL Server has a large percentage of fragmentation, then the recommended approach is to rebuild those indexes.
But what about when the DBA needs to rebuild all the indexes within a database? How can he achieve this? The techniques explained in that post provided ways of reorganizing/rebuilding specific indexes or all the indexes within a given table. In one of my previous posts, I talked about Index Fragmentation in SQL Server in terms of how to track it and how to resolve it.
0 kommentar(er)
