Monday, December 11, 2006


To iterate a command over all databases on a server, the undocumented sp_msforeachdb stored procedure fits the bill nicely. For example, I was given the task to set all databases on a particular server to a bulk-logged recovery model; this was easily accomplished with a single command:

master..sp_msforeachdb 'exec sp_dboption ''?'', ''select into/bulkcopy'', ''on'''

Note that this procedure executes dynamic SQL, replacing the ? with the name of the database as it iterates.

No comments: