Using sp_MSforeachdb and sp_MSforeachtable

Lets say you have a need to update the recovery model on all databases on a sql server to FULL.
We can do this in several ways:

1. Write a cursor to loop through all the databases and run the 'Alter database set recovery FULL' on each database.
2. Write individual SQL statements for each database and execute as a batch.
3. Use the undocumented stored procedure sp_MSforeachdb,which is what we are going to explore below. But before you use it, a word of caution. Note that this stored procedure may not be supported in the future from Microsoft.

declare @cmd1 varchar(500)
declare @cmd2 varchar(500)
set @cmd1 = 'if ''?'' <> ''tempdb'' print ''*** Processing DB ? ***'''
set @cmd2 = 'if ''?'' <> ''tempdb'' ALTER DATABASE ? SET RECOVERY FULL'

exec sp_MSforeachdb @command1=@cmd1,
@command2=@cmd2

You can use the sp_MSforeachtable in a similar fashion, yes , you guessed it!

Lets say you want to display the row counts of all tables in a database, here is how you can do it.

declare @cmd1 varchar(500)
set @cmd1 ='SELECT ''?'',count(*) FROM ?'
exec sp_MSforeachtable @command1 = @cmd1

For more details : http://www.databasejournal.com/features/mssql/article.php/3441031

Welcome to my blog!