HoustonFirefox (7/6/2010)

---

I personally hate cursors and try not to use them if at all possible. In keeping with our TSQL roots I submit:

DECLARE @Database VARCHAR(255)

DECLARE @DBList TABLE (DBName varchar(255), Processed CHAR(1))

-- Populate the in-memory table @DBList with all of the database names

insert @DBList

Select [name] , 'N' from master..sysdatabases

where [name] NOT IN( 'model','master','tempdb','msdb')

--select * from @DBList -- DEBUG: Run this to prove population was successful

-- Grab the first DB name from our in-memory table

While EXISTS (Select top 1 DBName from @DBList where Processed='N' order by DBName)

BEGIN

-- Get the DB Name into the @Database variable

Set @Database = (Select top 1 DBName from @DBList where Processed='N' order by DBName)

-- Do whatever awesome stuff with the database...

print @Database

-- Update the Processed flag to skip this DB on the next WHILE pass. Ever-decreasing list

Update @DBList set Processed='Y' where DBName = @Database

END

Efficient? Nope. Easy to write? Yep! Understandable for the DBA that replaces you? Absolutely!

Hope this helps 😉