SQL Maintenance Scripts

defragment_indexes







Version: SQL Server 7.0/2000
Created by: Alexander Chigrik

You can defragment all the indexes on all the tables in your database periodically
(for example, one time per week at Sunday) to reduce fragmentation. The DBCC
INDEXDEFRAG statement cannot automatically defragment all indexes on all the
tables in a database; it can only work on one table and one index at a time.
You can use this script to defragment all indexes in every table in the current
database.


RebuildAllIndexes







Version: SQL Server 7.0/2000
Created by: Alexander Chigrik
http://www.MSSQLCity.com/ - all about MS SQL
(SQL Server Articles, FAQ, Scripts, Tips and Test Exams).

This stored procedure can be used to rebuild all indexes in the particular
database. You can pass the database name and the fillfactor value into
RebuildAllIndexes stored procedure, as in the example below (if the database
name was not specified, the current database will be used, if the fillfactor
was not specified, the default fillfactor value will be used):

EXEC RebuildAllIndexes 'pubs', 70


CheckAllTables







Version: SQL Server 7.0/2000
Created by: Alexander Chigrik
http://www.MSSQLCity.com/ - all about MS SQL
(SQL Server Articles, FAQ, Scripts, Tips and Test Exams).

This stored procedure can be used to check the integrity of the data and index
pages for all user tables in the particular database. In comparison with DBCC
CHECKDB statement, this stored procedure takes less time to run, because only
user tables will be checked. You can pass the database name into CheckAllTables
stored procedure, as in the example below (if the database name was not specified,
the current database will be used):

EXEC CheckAllTables 'pubs'


ForEachUserTable







Version: SQL Server 7.0/2000
Created by: Alexander Chigrik
http://www.MSSQLCity.com/ - all about MS SQL
(SQL Server Articles, FAQ, Scripts, Tips and Test Exams).

This stored procedure can be used to run some work for each user tables in the
particular database.
You can pass the database name and the string to execute into ForEachUserTable
stored procedure, as in the example below (if the database name was not
specified, the current database will be used):

EXEC ForEachUserTable @dbname = 'pubs',
                      @str = "DBCC CHECKTABLE ('?')"


sp_lock2







Version: SQL Server 7.0/2000
Created by: Alexander Chigrik
http://www.MSSQLCity.com/ - all about MS SQL
(SQL Server Articles, FAQ, Scripts, Tips and Test Exams).

This stored procedure can be used instead of sp_lock system stored procedure to
return more detailed locking view (it can return user name, host name, database
name, object name, index name and object owner).
This is the example to use sp_lock2:

EXEC sp_lock2