Here’s the syntax:
EXEC sp_MSforeachdb @command1, @replacechar, @command2, @command3, @precommand, @postcommand
| Parameter | Desc |
| @command1 | nvarchar(2000) field specifying the first command to run against each database. May not be blank. |
| @replacechar | nchar(1) field specifying the character in the command parameters to replace with the database name. Defaults to ? |
| @command2 | nvarchar(2000) field specifying the 2nd command to run against each database |
| @command3 | nvarchar(2000) field specifying the 3rd command to run against each database |
| @precommand | nvarchar(2000) field specifying a command to be run prior any commands against any database |
| @postcommand | nvarchar(2000) field specifying a command to be run after running all commands |
–Example to print the names of all the databases on the server:
EXEC [sp_MSforeachdb] ‘PRINT ”Database: ?”’
Results:
Database: DKADatabase
Database: master
Database: model
Database: msdb
Database: Northwind
–Example to get all the open transactions for every database on the server:
DECLARE @command1 nvarchar(2000)
DECLARE @replacechar nchar(1)
DECLARE @command2 nvarchar(2000)
DECLARE @command3 nvarchar(2000)
DECLARE @precommand nvarchar(2000)
DECLARE @postcommand nvarchar(2000)— Set parameter values
SET @replacechar = ‘?’
SET @precommand = ‘PRINT ”Server: ” + @@SERVERNAME’
SET @command1 = ‘USE ? PRINT ”Database: ?”’
SET @command2 = ‘DBCC OPENTRAN(?) WITH NO_INFOMSGS PRINT” ” ‘
SET @command3 = ”
SET @postcommand = ‘SELECT GETDATE() AS [Completed]’
EXEC [sp_MSforeachdb] @command1, @replacechar, @command2, @command3, @precommand, @postcommandResults:
Server: DEV
Database: DKADatabase
No active open transactions.Database: master
No active open transactions.Database: model
No active open transactions.Database: msdb
No active open transactions.Database: Northwind
No active open transactions.Completed
——————————————————
2006-04-02 17:05:37.527
