There are many cases where a DBA needs to perform a task against all databases in a SQL Server instance. In order to avoid the process of just changing a part of the statement or switching to the target database and executing the T-SQL statement each time, there are some options that can help you.
One option is the undocumented stored procedure "sp_MSforeachdb
" which executes the given T-SQL statements against all databases within a SQL Server instance.
Let's see an example. In this example we want to find the physical location of all database files in a SQL Server instance.
In this case we can execute the below command:
EXECUTE master.sys.sp_MSforeachdb 'USE [?]; SELECT DB_NAME() as DBName,[name],[filename] FROM sysfiles'
The above command will execute the encapsulated T-SQL statement against all databases within the SQL Server instance.
Here's the sample output:
A second option is to run the below command that queries the sys.databases
catalog and dynamically builds the individual T-SQL statements for all databases:
SELECT 'SELECT '''+[Name]+''' AS DBName,[name],[filename] FROM '+[name]+'..sysfiles'
You can then execute these statements one by one and thus have more control:
* Note: Massively executing T-SQL statements against all databases is something that should be avoided because it is easy to do a mistake and affect all databases. Always carefully test your T-SQL scripts on Test environments. Always take backups of your data.
What are your views on the subject? Have something to share? Feel free to leave your comment!