Execute a Command in the Context of Each Database in SQL Server using sp_ineachd...
source link: https://www.tuicool.com/articles/hit/IjIfIvI
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
By: Aaron Bertrand | | Related Tips:More >T-SQL
Problem
In a previous tip, I showed you how to create a more reliable and flexible sp_MSforeachdb . This replacement avoids the problems inherent in the native solution where it skips databases, fails against databases with problematic identifiers (like CREATE DATABASE [1db'-.foo];
), and adds options (like only execute against system databases, databases in a certain recovery model, or databases matching a certain name pattern). The title of this tip sounds a lot like a duplicate, but it is not.
Recently, a question came up about why it doesn’t execute the command in the actual context of the database; the example was that SELECT DB_NAME()
reflects the name of the calling database over and over again, rather than the database where the command was expected to run. Many people assume that it should issue a USE currentdb;
command for you, but that’s not how it works, and so a function like DB_NAME()
will only ever run in the context of the caller.
Solution
My replacement works like this because I wanted to exactly mimic the behavior of the native system procedure. It takes your command, loops through each database, replaces every instance of ?
in the command with the current database name, and executes. The same symptom will happen if you run the following command:
USE some_database; GO EXEC dbo.sp_foreachdb N'SELECT COUNT(*) FROM sys.objects;';
You will get the same result every time. Why? Because it will count the objects in some_database every time. In order to make it look at the other databases, you have to include the replacement character (?):
USE some_database; GO EXEC dbo.sp_foreachdb N'SELECT COUNT(*) FROM ?.sys.objects;';
But it doesn’t have to be this way. We can also create a stored procedure that actually executes your command – without any replacement – in the context of each database, without having to add all the instances of ?
yourself, or manually adding USE [?];
to the beginning of each command. Both of these can be troublesome if you are generating scripts or deriving them from a source and hoping to execute them without manipulation.
And so, I present a stored procedure that does this, with a working title of sp_ineachdb
. The name reflects that it executes a command in the context of each database, instead of simply for each database. For now, I’ll leave out all the options I added to the original replacement:
USE [master]; GO CREATE PROCEDURE dbo.sp_ineachdb @command nvarchar(max) AS BEGIN DECLARE @context nvarchar(150), @sx nvarchar(18) = N'.sys.sp_executesql', @db sysname; CREATE TABLE #dbs(name sysname PRIMARY KEY); INSERT #dbs(name) SELECT QUOTENAME(name) FROM sys.databases WHERE [state] & 992 = 0 - accessible AND DATABASEPROPERTYEX(name, 'UserAccess') <> 'SINGLE_USER' AND HAS_DBACCESS(name) = 1; DECLARE dbs CURSOR LOCAL FAST_FORWARD FOR SELECT name, name + @sx FROM #dbs; OPEN dbs; FETCH NEXT FROM dbs INTO @db, @context; DECLARE @msg nvarchar(512) = N'Could not run against %s : %s.', @err nvarchar(max); WHILE @@FETCH_STATUS <> -1 BEGIN BEGIN TRY EXEC @context @command = @command; END TRY BEGIN CATCH SET @err = ERROR_MESSAGE(); RAISERROR(@msg, 1, 0, @db, @err); END CATCH FETCH NEXT FROM dbs INTO @db, @context; END CLOSE dbs; DEALLOCATE dbs; END GO EXEC sys.sp_MS_marksystemobject N'sp_ineachdb';
Stepping through the code, here’s what it does:
- Creates a table to hold the database names
- Inserts all the databases that are accessible, online, not
single_user
, and that the caller can access - Creates a cursor to step through each name
- For each database:
- Tries to execute the command in each database (I’ll explain how that works in a moment)
- If it fails, raise a non-batch-aborting error (essentially a print stating what happened.
That’s it. Now, how the execution works, is that we can execute in the context of another database (or server even) by creating a string that represents [location].sys.sp_executesql
. For example:
USE [master]; GO DECLARE @context nvarchar(150) = N'tempdb.sys.sp_executesql'; EXEC @context @command = N'PRINT DB_NAME();';
What this is really doing is:
EXEC tempdb.sys.sp_executesql @command = N'PRINT DB_NAME();';
This executes the print statement in the context of tempdb, without ever having to switch to tempdb. In sp_ineachdb
, we’re simply taking advantage of this by building @context
dynamically. The QUOTENAME()
protects you from bad identifiers, so go ahead and try this with a bunch of poorly-named databases:
CREATE DATABASE [12345]; CREATE DATABASE [1db'-.foo]; CREATE DATABASE [bad"name]; CREATE DATABASE [bad.name]; CREATE DATABASE [bad-name]; CREATE DATABASE [bad]][[.name]; CREATE DATABASE [bad[]]name]; CREATE DATABASE [bad name]; CREATE DATABASE [ leading space]; CREATE DATABASE [trailing space ];
These all work no problem:
EXEC dbo.sp_ineachdb @command = N'PRINT DB_NAME();';
Output:
leading space 12345 1db'-.foo bad name bad"name bad.name bad[]name bad][[.name bad-name master model msdb tempdb trailing space
This means that if you do want the calling context to mean something (say, you have a static, central table you want to join against everything else in all the other databases, you need to qualify the calling context in the command. As an example, you want to find all the objects with names that match a central list, which you store in a table in a database called Central
.
CREATE TABLE dbo.ObjectNameBlacklist ( badname sysname ); INSERT dbo.ObjectNameBlacklist(badname) VALUES(N'badword');
With sp_foreachdb
, you would say:
EXEC dbo.sp_foreachdb N'SELECT ''?'', badname FROM dbo.ObjectNameBlacklist AS onb WHERE EXISTS ( SELECT 1 FROM ?.sys.objects WHERE name = onb.name COLLATE DATABASE_DEFAULT );';
(And admittedly you would get an error on at least one of the bad database names from above.)
With the new procedure, you would say (differences bolded):
EXEC dbo.sp_ineachdb N'SELECT DB_NAME(), badname FROM Central.dbo.ObjectNameBlacklist AS onb WHERE EXISTS ( SELECT 1 FROM sys.objects WHERE name = onb.name COLLATE DATABASE_DEFAULT );';
This one does not fail on any of the bad database names, showing how much more resilient QUOTENAME()
is compared to manually constructing your own quoted identifiers. More importantly, you can now pass commands without worrying about ?
, how to escape it, or adding USE
commands.
Another nice benefit is that nested INSERT … EXEC works. With sp_foreachdb, if you try this:
CREATE TABLE #c(db sysname, c int); INSERT #c EXEC dbo.sp_foreachdb N'SELECT ''?'', COUNT(*) FROM ?.sys.objects;'; SELECT db,c FROM #c; GO DROP TABLE #c;
The result is an error message:
Msg 8164, Level 16, State 1, Procedure dbo.sp_foreachdb, Line 87 [Batch Start Line 0]
An INSERT EXEC statement cannot be nested.
With at least this simple version of sp_ineachdb
, it works, because there isn’t a nested INSERT … EXEC
:
CREATE TABLE #c(db sysname, c int); INSERT #c EXEC dbo.sp_ineachdb N'SELECT DB_NAME(), COUNT(*) FROM sys.objects;'; SELECT db,c FROM #c; GO DROP TABLE #c;
Summary
It is definitely possible to look at the “execute the same command against a bunch of databases” problem in a variety of ways. This stored procedure shows how to execute the command in each database’s context, rather than expect the command to provide tokens to slip the database name into. Depending on what you need to do and where the commands are coming from, this might be a better option, as it overcomes multiple limitations of both the native procedure and my replacement.
You can easily extend this solution by adding the other options I added for sp_foreachdb
, to better dictate which databases to execute against. I didn’t do that here because I wanted to simplify the above to demonstrate how it works; I will address that in a follow-up tip (and will try to find a way to resolve the nested INSERT … EXEC
issue).
Next Steps
Read on for related tips and other resources:
- Making a more reliable and flexible sp_MSforeachdb
- Run The Same SQL Command Against All SQL Server Databases
- Iterating through SQL Server databases and database objects without cursors
- Common uses for sp_MSforeachdb
Last Update: 2018-10-04
About the author
Aaron Bertrand (@AaronBertrand) is a Product Manager at SentryOne, with industry experience dating back to Classic ASP and SQL Server 6.5. He is editor-in-chief of the performance-related blog, SQLPerformance.com, and serves as a community moderator for the Database Administrators Stack Exchange.
View all my tipsRelated Resources
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK