There are times when i find myself needing to run a SQL command against each database on one of my SQL server instances. There is a handy undocumented stored procedure that allows you to do this with a single line statement and does not need using cursors either.
Syntax for this undocumented procedure is:
EXEC sp_MSforeachdb @command
Example: Query information from all databases on a SQL instance
--This query will return a listing of all tables in all databases on a SQL instance:
DECLARE @command varchar(1000)
SELECT @command = 'USE ? SELECT name FROM sysobjects WHERE xtype = ''U'' ORDER BY name'
EXEC sp_MSforeachdb @command
You can exclude the @command variable. The command below also behaves the same as above
--This query will return a listing of all tables in all databases on a SQL instance:
EXEC sp_MSforeachdb 'USE ? SELECT name FROM sysobjects WHERE xtype = ''U'' ORDER BY name'
When I inherit a new SQL server to manage, one of the things i look for is the recovery model of the databases. This has caused me calls from sys admin teams stating that the disk is full. Few times the culprit is that the recovery model is set to the default as FULL. So you are logging every single transaction.
Unless you are running regular transaction log backups, it is recommended to switch the recovery model to SIMPLE. This option will discard the log as soon as a transaction is committed and written to the disk.
The following command will set the database recovery model on all user databases to SIMPLE.
exec sp_MSforeachdb 'IF ''?'' NOT IN (''master'',''tempdb'',''model'',''msdb'') BEGIN ALTER AUTHORIZATION on database::? SET RECOVERY SIMPLE END'
You are welcome to take the above one line command and modify the filtering above to add or remove the databases per your needs.
Database Blog 