Run same command on all SQL server databases

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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: