Ramblings of a DBA

Tara Kizer
posts - 166, comments - 837, trackbacks - 75

My Links

Advertisement

News

Subscribe
Search this Blog

Archives

Post Categories

Work

Running my code against multiple databases

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.

Print | posted on Wednesday, November 28, 2007 11:53 AM | Filed Under [ SQL Server - Database Administration ]

Feedback

Gravatar

# re: Running my code against multiple databases

If anyone's interested, I have an old article here that shows how you could do this from the command line:

http://weblogs.sqlteam.com/robv/articles/4099.aspx

It's a simple modification to use a text file or typed list of database names. Maybe this might inspire me to finish the rest of the articles. :)
11/29/2007 2:18 PM | robvolk
Gravatar

# re: Running my code against multiple databases

He lives!
11/29/2007 2:23 PM | Tara
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET