Often as a DBA I am required to perform a particular task on every database, or some subset of databases. If it is the same task for each database then it makes sense to automate this in some way.
Two ways to do this are using the undocumented sp_MSforeachdb stored procedure and the other is with a sort of DBA assisted dynamic SQL (I am co-opting this term with no other authorization than this is how I think of it).
The first method is useful when a simple action is being done to all databases, while the second method is a little easier to filter and gives the DBA a bit more granular control.
Let’s for the sake of something to do consider changing recovery models into full.
Using sp_MSforeachdb the command is simple:
exec master..sp_MSforeachdb 'ALTER DATABASE ? SET RECOVERY FULL';
Two things to notice: The question mark acts as the place holder for the database name. This command will change all appropriate databases into full recovery but will also throw an error because it will also attempt to do this for tempdb and any database snapshots.
My favourite method is to use what I think of as DBA assisted dynamic SQL. Let’s perform the same action.
Start by querying the sys.databases catalog view for the databases we want to edit:
select name from sys.databases where recovery_model_desc != 'FULL';
Now we build a query that concatenates the names from this list into a series of alter database statements:
select 'ALTER DATABASE ' + name + ' SET RECOVERY FULL;' from sys.databases where recovery_model_desc != 'FULL';
This query was created simply by adding the strings on to either side of the name field from the original sys.databases query. The output is a series of alter database statements that can be copied and pasted into another query window.
What I like about this approach is that the DBA has a bit more granular control. I can scan the list and make any changes (like excluding tempdb) before I run the queries. I can choose to run as one batch or to run one at a time. I also have the option of copying this script out to use at a later date.
I find this option to be more versatile and easier to filter than the sp_MSforeachdb procedure (although this does have its place). The sp_MSforeachdb lends itself more easily to being scheduled as an automated job whereas the second method requires a little wrangling to pipe the output into an executeSQL statement.