How to apply or execute a T-SQL command to every database in your server
We talked before on How to apply or execute a T-SQL command to every tables in your database.
Now we are going to have a quick look on the same but for databases not tables …
There is a handy undocumented stored procedure that allows you to do this without needing to set up a cursor against your sysdatabases table in the master database: sp_MSforeachdb SP.
Examples for how to use it:
- To 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'
- To creates a stored procedure in each user database that will return a listing of all users in a database:
EXEC sp_MSforeachdb 'IF ''?'' NOT IN(''master'', ''model'', ''msdb'', ''tempdb'') BEGIN USE ? EXEC(''CREATE PROCEDURE pNewProcedure1 AS SELECT name, createdate, updatedate FROM sys.sysusers'') END'Here is a tip in this example on how to exclude system databases while executing a certain query.
- To return a listing of all files in all databases on a SQL instance
EXEC sp_MSforeachdb 'USE ? SELECT ''?'', SF.filename, SF.size FROM sys.sysfiles SF'
- To shrink database and truncate logs of every database
EXEC sp_MSforeachdb 'IF ''?'' NOT IN(''master'', ''model'', ''tempdb'', ''msdb'') BEGIN USE ? DBCC SHRINKFILE( ?_log, 1 ) BACKUP LOG ? WITH TRUNCATE_ONLY DBCC SHRINKFILE( ?_log, 1 ) END'
