Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin.
Search for scripts directly from SSMS, and instantly access any saved scripts in your
SSC briefcase from the favorites tab.
Download now (direct download link)
Thank this author by sharing:
By Luke Campbell,
The first procedure is based on the process found in Books Online. However the cursor has been replaced with a while loop and modified to accept a database name as a parameter. Using dynamic sql I was able to overcome the issue in having to deploy to each db on the server. The procedure will REORGANIZE any indexes that have a fragementation level between 20 & 30 percent and has a page count greater than 10. The REBUILD parameter will be used if the fragmentation level is 30 percent or above. I have also added the ability to log information into a table regarding start & end times (currently set to Insert into DBInfo in a database named MAINT). Uses the SORT_IN_TEMPDB and ONLINE options (Please see BOL for more information on these options before deploying to your environment).
The second procedure will cycle through each non system database on the server and execute the uspIndexMaintenance script.
stored procedures in a database
Procedure to see database status
How to have a Stored Procedure work on another database
DATABASE NAME AS PARAMETER IN PROCEDURE
Search for a text in all the stored Procedures in a Database