Friday, February 24, 2017

Executing T-SQL Statements Against All Databases

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'
FROM sys.databases



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.

The SQL Server and .NET Hub

Reference: The SQL Server and .NET Hub (http://www.sqlnethub.com)

What are your views on the subject? Have something to share? Feel free to leave your comment!


Artemakis Artemiou [MVP]

Author & Editor

Artemakis Artemiou is a Senior SQL Server Architect, Software Developer and Microsoft Data Platform MVP. He is also an author, regular blogger, president of Cyprus.NET User Group and CY Country leader of INETA-EU. He is also the creator of DBA Security Advisor and In-Memory OLTP Simulator. Artemakis is a frequent guest author of worldwide well-respected online journals where he writes articles focusing on many SQL Server topics.

Reference: The SQL Server and .NET Hub (http://www.sqlnethub.com)

0 comments: