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
sp_MSforeachdb
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:
| 1 2 3 4 | EXEC master.sys.sp_MSforeachdb ' USE [?] SELECT DB_NAME()' |
Displaying datafiles for each database
| 1 | EXEC master.sys.sp_MSforeachdb 'SELECT * FROM [?].sys.database_files' |
Displaying the datafiles of each database that contains the word “Cobranca”
| 1 2 3 4 | EXEC master.sys.sp_MSforeachdb ' IF (''[?]'' LIKE ''%Cobranca%'') SELECT * FROM [?].sys.database_files ' |
Displaying the names of databases, schemas, and tables that begin with Client in your instance.
| 1 2 3 4 5 6 7 | EXEC master.sys.sp_MSforeachdb ' USE [?]; IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ''Cliente'') > 0) BEGIN SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE ''Cliente%'' END' |
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:
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 | IF (OBJECT_ID('tempdb..#Databases') IS NOT NULL) DROP TABLE #Databases SELECT [name], [dbid], IDENTITY(INT, 1, 1) AS Ordem INTO #Databases FROM sys.sysdatabases WITH(NOLOCK) ORDER BY name DECLARE @Qt_Databases INT = (SELECT COUNT(*) FROM #Databases), @Contador INT = 1, @Ds_Database SYSNAME, @Ds_Query VARCHAR(MAX) = 'SELECT * FROM sys.database_files', @Cmd VARCHAR(MAX) WHILE(@Contador <= @Qt_Databases) BEGIN SELECT @Ds_Database = name FROM #Databases WHERE Ordem = @Contador SET @Cmd = 'USE [' + @Ds_Database + ']; ' + CHAR(10) + @Ds_Query EXEC(@Cmd) SET @Contador = @Contador + 1 END |
Example 2:
After creating the SP below, returns the name of all databases and writes them to a temporary table.
| 1 2 3 4 5 6 7 8 | IF (OBJECT_ID('tempdb..#Dbs') IS NOT NULL) DROP TABLE #Dbs CREATE TABLE #Dbs ( Nome SYSNAME ) INSERT INTO #Dbs EXEC dbo.stpExecuta_Query_Databases @Ds_Query = 'SELECT DB_NAME()' |
Returns the name of all databases that contain "Cobranca" in the name
| 1 2 3 | EXEC dbo.stpExecuta_Query_Databases @Ds_Query = 'SELECT DB_NAME()', @Ds_Incluir_Database = '%Cobranca%' |
Returns the name of all databases that do NOT contain "Cobranca" in the name
| 1 2 3 | EXEC dbo.stpExecuta_Query_Databases @Ds_Query = 'SELECT DB_NAME()', @Ds_Excluir_Database = '%Cobranca%' |
Stored Procedure Code stpExecuta_Query_Databases:
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 | CREATE PROCEDURE dbo.stpExecuta_Query_Databases ( @Ds_Query VARCHAR(MAX), @Ds_Incluir_Database VARCHAR(MAX) = NULL, @Ds_Excluir_Database VARCHAR(MAX) = NULL ) AS BEGIN IF (OBJECT_ID('tempdb..#Databases') IS NOT NULL) DROP TABLE #Databases CREATE TABLE #Databases ( [name] SYSNAME, [database_id] INT, [Ordem] INT IDENTITY(1, 1) ) IF (@Ds_Incluir_Database IS NULL AND @Ds_Excluir_Database IS NULL) BEGIN INSERT INTO #Databases SELECT [name], [database_id] FROM sys.databases WITH(NOLOCK) WHERE state_desc = 'ONLINE' ORDER BY name END ELSE BEGIN IF (@Ds_Incluir_Database IS NOT NULL) BEGIN INSERT INTO #Databases SELECT [name], [database_id] FROM sys.databases WITH(NOLOCK) WHERE [name] LIKE (@Ds_Incluir_Database) AND state_desc = 'ONLINE' ORDER BY name END ELSE BEGIN INSERT INTO #Databases SELECT [name], [database_id] FROM sys.databases WITH(NOLOCK) WHERE [name] NOT LIKE (@Ds_Excluir_Database) AND state_desc = 'ONLINE' ORDER BY name END END DECLARE @Qt_Databases INT = (SELECT COUNT(*) FROM #Databases), @Contador INT = 1, @Ds_Database SYSNAME, @Cmd VARCHAR(MAX) WHILE(@Contador <= @Qt_Databases) BEGIN SELECT @Ds_Database = name FROM #Databases WHERE Ordem = @Contador SET @Cmd = 'USE [' + @Ds_Database + ']; ' + CHAR(10) + @Ds_Query EXEC(@Cmd) SET @Contador = @Contador + 1 END END |
Good afternoon!
Do you have any reference that sp_MSforeachdb bypasses some banks as mentioned? I have this problem here in the company, but if I just say that there is a problem they will want me to give evidence.
Thank you.
Good afternoon, a simple if:
EXEC SP_MSFOREACHDB 'USE [?]
—
IF (DB_NAME() IN ("BASE1", "BASE2")) BEGIN
— YOUR CODE HERE
END
Dirceu! Fantastic this SP and your article! I didn't really know her and it really saved me a lot of time and code for a simple survey I needed to do on PK and FK amount of 25 databases for basic structure comparison on a timely basis.
Thank you so much for your contribution !!! Congratulations for the excellent article!
Thanks for the feedback, Herica 🙂
Congratulations answered until the doubts I was going to have