MSSQLCity.Com - All about MS SQL
     
About Us  
Articles  
FAQ  
Scripts  
Import/Export    
Maintenance  
T-SQL  
SQL 6.5  
Tips  
Test Exams  
Advertise  
Download  
History  
Search  
Traffic  
Related Links  
     
Your button logo
Add to Favorites
 
     
 

Maintenance Scripts


Disclaimer: Use these scripts at your own risk. All information on these pages is provided "AS IS", without any warranty. MSSQLCity.Com shall not be liable for any damages you may sustain by using this information.
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
 

 
Visit The SQL Server Worldwide User's Group for all the latest news and information about SQL Server, Oracle, DB2 and XML for developers and administrators.

(c) 1997, 2010 Bits on the Wire, Inc