Click on the banner to learn about and purchase my database training on Azure

Running a command on all instance databases in SQL Server

Views: 5.385 views
Reading Time: 3 minutes

Speak guys!
All right, right?

Today I will show you how to execute a query on all databases of a SQL Server instance. This is often useful for creating space monitoring routines, running a checkdb on all banks, and many other day-to-day needs.

To accomplish this activity there are several different ways of programming, but I will cover two of the most used

The sp_MSforeachdb procedure comes standard with SQL Server, but has no official documentation from Microsoft so far, so many people are unaware of its existence or how to use it. One of the most important details in its use is wildcart?, Which will be named after the database being iterated by the Stored Procedure sp_MSforeachdb.

Let's take a very basic example that will just display the name of each database:

Displaying datafiles for each database

Displaying the datafiles of each database that contains the word “Cobranca”

Displaying the names of databases, schemas, and tables that begin with Client in your instance.

Using SQL Server Catalog Views
The sp_MSforeachdb system procedure is very useful in everyday DBA and very easy to use, but I've seen some cases where the database skips some databases during iteration when there is a very large server overload. Therefore, I will show another approach to meet this need.

Example 1:

Example 2:
After creating the SP below, returns the name of all databases and writes them to a temporary table.

Returns the name of all databases that contain "Cobranca" in the name

Returns the name of all databases that do NOT contain "Cobranca" in the name

Stored Procedure Code stpExecuta_Query_Databases: