I've received a few comments in the past about people modifying my code so that it loops through the databases rather than having to call my code for each database. One such example is with my isp_ALTER_INDEX stored procedure. In my version, you provide the database name as an input parameter. I recently received a comment that someone had modified my code so that it did not have this input parameter and instead had code added to it that looped through the databases to be defragmented. The disadvantage to this is that I update my code occassionally to fix bugs or to improve it. It is much easier to grab my update as is than to incorporate my code changes into your modified version.
Here is the code that I use in my "Defragment Indexes" job:
SET NOCOUNT ON
DECLARE @dbName sysname, @rc int
SELECT name
INTO #db
FROM sys.databases
WHERE
name NOT IN ('master', 'model', 'msdb', 'tempdb', 'Admin', 'OnePoint') AND
name NOT LIKE '%ReportServer%' AND
DATABASEPROPERTYEX([name], 'IsInStandBy') = 0 AND
DATABASEPROPERTYEX([name], 'Status') = 'ONLINE'
SELECT @rc = 1, @dbName = MIN(name)
FROM #db
WHILE @rc <> 0
BEGIN
EXEC Admin.dbo.isp_ALTER_INDEX
@dbName = @dbName,
@statsMode = 'SAMPLED',
@defragType = 'REBUILD',
@minFragPercent = 10,
@maxFragPercent = 100,
@minRowCount = 1000
SELECT TOP 1 @dbName = name
FROM #db
WHERE name > @dbName
ORDER BY name
SET @rc = @@ROWCOUNT
END
DROP TABLE #db
There are many ways to achieve this same functionality. You could also use sp_MSforeachdb to do the looping, however we want more control than that as we want the ability to exclude databases such as the system ones.