sp_msforeach db - Why do we need to use the USE keyword

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty margin-bottom:0;

up vote
6
down vote

favorite

sp_msforeachdb is an undocumented sp which is designed to run some T-SQL against every database in the server instance. Why then, does it appear that I need to use the USE keyword to do that

EXEC sp_MSForEachDb @command1 = 'SELECT DB_NAME()'

Prints the database name the sp_MSForEachDb command was run against n times, where n is the number of databases on the instance.

EXEC sp_MSForEachDb @command1 = 'USE ?; SELECT DB_NAME()'

Prints the name of each database.

Why is it necessary to use the USE statement? Shouldn't this behavior be inherent in the procedure?

edited Sep 7 at 14:20

MDCCL

6,30731640

asked Sep 7 at 11:38

SEarle1986

29811

add a commentÂ |Â

up vote
6
down vote

favorite

sp_msforeachdb is an undocumented sp which is designed to run some T-SQL against every database in the server instance. Why then, does it appear that I need to use the USE keyword to do that

EXEC sp_MSForEachDb @command1 = 'SELECT DB_NAME()'

Prints the database name the sp_MSForEachDb command was run against n times, where n is the number of databases on the instance.

EXEC sp_MSForEachDb @command1 = 'USE ?; SELECT DB_NAME()'

Prints the name of each database.

Why is it necessary to use the USE statement? Shouldn't this behavior be inherent in the procedure?

edited Sep 7 at 14:20

MDCCL

6,30731640

asked Sep 7 at 11:38

SEarle1986

29811

add a commentÂ |Â

up vote
6
down vote

favorite

sp_msforeachdb is an undocumented sp which is designed to run some T-SQL against every database in the server instance. Why then, does it appear that I need to use the USE keyword to do that

EXEC sp_MSForEachDb @command1 = 'SELECT DB_NAME()'

Prints the database name the sp_MSForEachDb command was run against n times, where n is the number of databases on the instance.

EXEC sp_MSForEachDb @command1 = 'USE ?; SELECT DB_NAME()'

Prints the name of each database.

Why is it necessary to use the USE statement? Shouldn't this behavior be inherent in the procedure?

edited Sep 7 at 14:20

MDCCL

6,30731640

asked Sep 7 at 11:38

SEarle1986

29811

sp_msforeachdb is an undocumented sp which is designed to run some T-SQL against every database in the server instance. Why then, does it appear that I need to use the USE keyword to do that

EXEC sp_MSForEachDb @command1 = 'SELECT DB_NAME()'

Prints the database name the sp_MSForEachDb command was run against n times, where n is the number of databases on the instance.

EXEC sp_MSForEachDb @command1 = 'USE ?; SELECT DB_NAME()'

Prints the name of each database.

Why is it necessary to use the USE statement? Shouldn't this behavior be inherent in the procedure?

edited Sep 7 at 14:20

MDCCL

6,30731640

asked Sep 7 at 11:38

SEarle1986

29811

edited Sep 7 at 14:20

MDCCL

6,30731640

edited Sep 7 at 14:20

MDCCL

6,30731640

edited Sep 7 at 14:20

MDCCL

6,30731640

asked Sep 7 at 11:38

SEarle1986

29811

asked Sep 7 at 11:38

SEarle1986

29811

asked Sep 7 at 11:38

SEarle1986

29811

add a commentÂ |Â

3 Answers
3

active

oldest

votes

up vote
11
down vote

accepted

The procedure does not perform a USE command for you. The way the procedure works is that it replaces every ? in your command with the database prefix.

If you run this:

USE foodb;
GO
EXEC sys.sp_MSforeachdb N'SELECT * FROM sys.objects;'; 
-- 3,380 total rows on my system

You will also get a number of resultsets that all show the objects from foodb. You have to issue the command this way in order to get the command to execute in the context of each individual database:

EXEC sys.sp_MSforeachdb N'SELECT * FROM ?.sys.objects;'; 
-- 50,603 total rows on my system

In this case it will execute your command for each database, with the ? replaced by the database name:

SELECT * FROM master.sys.objects;
SELECT * FROM tempdb.sys.objects;
...

To call a system function that doesn't support a database prefix typically requires a USE command first. A way to do this differently could be:

EXEC sys.sp_MSforeachdb N'SELECT DB_NAME(DB_ID(''?''));';

Or, more simply:

EXEC sys.sp_MSforeachdb N'SELECT N''?'';';

One reason it works this way is that you might be executing from the context of the current database because it has a static object you want to use in relation to all of the databases. So, imagine you are in foodb and you create this table:

CREATE TABLE dbo.ObjectNameBlacklist
(
 name sysname
);

INSERT dbo.ObjectNameBlacklist(name) VALUES('badword');

You want to identify all the objects in any database that match the names in this table. So you can say:

EXEC sys.sp_MSforeachdb N'SELECT ''?'', name 
 FROM dbo.ObjectNameBlacklist AS onb
 WHERE EXISTS
 (
 SELECT 1 FROM ?.sys.objects WHERE name = onb.name
 );';

You wouldn't want the command to look for dbo.ObjectNameBlacklist in each database. Of course you could prefix that one manually, but you don't have to because of the way the procedure works.

answered Sep 7 at 12:10

Aaron Bertrandâ™¦

144k19276463

add a commentÂ |Â

up vote
4
down vote

If you look at the sp_helptext entry for sp_MSforeachdb, or its weird friend sp_MSforeachtable

EXEC sys.sp_helptext @objname = N'sp_MSforeachdb';
EXEC sys.sp_helptext @objname = N'sp_MSforeachtable';

You'll see they're both just wrappers for sp_MSforeach_worker:

EXEC sys.sp_helptext @objname = N'sp_MSforeach_worker';

All they do is build up a valid <list of things>, but they don't actually loop over them in a meaningful way.

At any rate, Aaron Bertrand's sp_foreachdb is a much better piece of code that doesn't skip databases, etc.

edited Sep 7 at 12:26

answered Sep 7 at 12:10

sp_BlitzErik

19.5k1161101

add a commentÂ |Â

up vote
2
down vote

If you run sp_helptext for the procedure, you will see that it creates a cursor with databases' names and then runs sp_MSforeach_worker.
Comment from sp_MSforeach_worker:

This is the worker proc for all of the "for each" type procs. Its
function is to read the next replacement name from the cursor (which
returns only a single name), plug it into the replacement locations
for the commands, and execute them. It assumes the cursor
"hCForEach***" has already been opened by its caller. worker_type
is a parameter that indicates whether we call this for a database (1)
or for a table (0)

So, sp_msforeachdb doesn't run a query on each database but runs a query with replaced '?' with database name for each database from master.dbo.sysdatabases.

answered Sep 7 at 12:15

Denis Rubashkin

2357

add a commentÂ |Â

Your Answer

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "182"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
convertImagesToLinks: false,
noModals: false,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);

);

draft saved

draft discarded

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f216999%2fsp-msforeach-db-why-do-we-need-to-use-the-use-keyword%23new-answer', 'question_page');

);

Post as a guest

Name

3 Answers
3

active

oldest

votes

3 Answers
3

active

oldest

votes

up vote
11
down vote

accepted

The procedure does not perform a USE command for you. The way the procedure works is that it replaces every ? in your command with the database prefix.

If you run this:

USE foodb;
GO
EXEC sys.sp_MSforeachdb N'SELECT * FROM sys.objects;'; 
-- 3,380 total rows on my system

EXEC sys.sp_MSforeachdb N'SELECT * FROM ?.sys.objects;'; 
-- 50,603 total rows on my system

In this case it will execute your command for each database, with the ? replaced by the database name:

SELECT * FROM master.sys.objects;
SELECT * FROM tempdb.sys.objects;
...

To call a system function that doesn't support a database prefix typically requires a USE command first. A way to do this differently could be:

EXEC sys.sp_MSforeachdb N'SELECT DB_NAME(DB_ID(''?''));';

Or, more simply:

EXEC sys.sp_MSforeachdb N'SELECT N''?'';';

CREATE TABLE dbo.ObjectNameBlacklist
(
 name sysname
);

INSERT dbo.ObjectNameBlacklist(name) VALUES('badword');

You want to identify all the objects in any database that match the names in this table. So you can say:

EXEC sys.sp_MSforeachdb N'SELECT ''?'', name 
 FROM dbo.ObjectNameBlacklist AS onb
 WHERE EXISTS
 (
 SELECT 1 FROM ?.sys.objects WHERE name = onb.name
 );';

You wouldn't want the command to look for dbo.ObjectNameBlacklist in each database. Of course you could prefix that one manually, but you don't have to because of the way the procedure works.

answered Sep 7 at 12:10

Aaron Bertrandâ™¦

144k19276463

add a commentÂ |Â

up vote
11
down vote

accepted

The procedure does not perform a USE command for you. The way the procedure works is that it replaces every ? in your command with the database prefix.

If you run this:

USE foodb;
GO
EXEC sys.sp_MSforeachdb N'SELECT * FROM sys.objects;'; 
-- 3,380 total rows on my system

EXEC sys.sp_MSforeachdb N'SELECT * FROM ?.sys.objects;'; 
-- 50,603 total rows on my system

In this case it will execute your command for each database, with the ? replaced by the database name:

SELECT * FROM master.sys.objects;
SELECT * FROM tempdb.sys.objects;
...

To call a system function that doesn't support a database prefix typically requires a USE command first. A way to do this differently could be:

EXEC sys.sp_MSforeachdb N'SELECT DB_NAME(DB_ID(''?''));';

Or, more simply:

EXEC sys.sp_MSforeachdb N'SELECT N''?'';';

CREATE TABLE dbo.ObjectNameBlacklist
(
 name sysname
);

INSERT dbo.ObjectNameBlacklist(name) VALUES('badword');

You want to identify all the objects in any database that match the names in this table. So you can say:

EXEC sys.sp_MSforeachdb N'SELECT ''?'', name 
 FROM dbo.ObjectNameBlacklist AS onb
 WHERE EXISTS
 (
 SELECT 1 FROM ?.sys.objects WHERE name = onb.name
 );';

You wouldn't want the command to look for dbo.ObjectNameBlacklist in each database. Of course you could prefix that one manually, but you don't have to because of the way the procedure works.

answered Sep 7 at 12:10

Aaron Bertrandâ™¦

144k19276463

add a commentÂ |Â

up vote
11
down vote

accepted

The procedure does not perform a USE command for you. The way the procedure works is that it replaces every ? in your command with the database prefix.

If you run this:

USE foodb;
GO
EXEC sys.sp_MSforeachdb N'SELECT * FROM sys.objects;'; 
-- 3,380 total rows on my system

EXEC sys.sp_MSforeachdb N'SELECT * FROM ?.sys.objects;'; 
-- 50,603 total rows on my system

In this case it will execute your command for each database, with the ? replaced by the database name:

SELECT * FROM master.sys.objects;
SELECT * FROM tempdb.sys.objects;
...

To call a system function that doesn't support a database prefix typically requires a USE command first. A way to do this differently could be:

EXEC sys.sp_MSforeachdb N'SELECT DB_NAME(DB_ID(''?''));';

Or, more simply:

EXEC sys.sp_MSforeachdb N'SELECT N''?'';';

CREATE TABLE dbo.ObjectNameBlacklist
(
 name sysname
);

INSERT dbo.ObjectNameBlacklist(name) VALUES('badword');

You want to identify all the objects in any database that match the names in this table. So you can say:

EXEC sys.sp_MSforeachdb N'SELECT ''?'', name 
 FROM dbo.ObjectNameBlacklist AS onb
 WHERE EXISTS
 (
 SELECT 1 FROM ?.sys.objects WHERE name = onb.name
 );';

You wouldn't want the command to look for dbo.ObjectNameBlacklist in each database. Of course you could prefix that one manually, but you don't have to because of the way the procedure works.

answered Sep 7 at 12:10

Aaron Bertrandâ™¦

144k19276463

The procedure does not perform a USE command for you. The way the procedure works is that it replaces every ? in your command with the database prefix.

If you run this:

USE foodb;
GO
EXEC sys.sp_MSforeachdb N'SELECT * FROM sys.objects;'; 
-- 3,380 total rows on my system

EXEC sys.sp_MSforeachdb N'SELECT * FROM ?.sys.objects;'; 
-- 50,603 total rows on my system

In this case it will execute your command for each database, with the ? replaced by the database name:

SELECT * FROM master.sys.objects;
SELECT * FROM tempdb.sys.objects;
...

To call a system function that doesn't support a database prefix typically requires a USE command first. A way to do this differently could be:

EXEC sys.sp_MSforeachdb N'SELECT DB_NAME(DB_ID(''?''));';

Or, more simply:

EXEC sys.sp_MSforeachdb N'SELECT N''?'';';

CREATE TABLE dbo.ObjectNameBlacklist
(
 name sysname
);

INSERT dbo.ObjectNameBlacklist(name) VALUES('badword');

You want to identify all the objects in any database that match the names in this table. So you can say:

EXEC sys.sp_MSforeachdb N'SELECT ''?'', name 
 FROM dbo.ObjectNameBlacklist AS onb
 WHERE EXISTS
 (
 SELECT 1 FROM ?.sys.objects WHERE name = onb.name
 );';

You wouldn't want the command to look for dbo.ObjectNameBlacklist in each database. Of course you could prefix that one manually, but you don't have to because of the way the procedure works.

answered Sep 7 at 12:10

Aaron Bertrandâ™¦

144k19276463

answered Sep 7 at 12:10

Aaron Bertrandâ™¦

144k19276463

answered Sep 7 at 12:10

Aaron Bertrandâ™¦

144k19276463

answered Sep 7 at 12:10

Aaron Bertrandâ™¦

144k19276463

add a commentÂ |Â

up vote
4
down vote

If you look at the sp_helptext entry for sp_MSforeachdb, or its weird friend sp_MSforeachtable

EXEC sys.sp_helptext @objname = N'sp_MSforeachdb';
EXEC sys.sp_helptext @objname = N'sp_MSforeachtable';

You'll see they're both just wrappers for sp_MSforeach_worker:

EXEC sys.sp_helptext @objname = N'sp_MSforeach_worker';

All they do is build up a valid <list of things>, but they don't actually loop over them in a meaningful way.

At any rate, Aaron Bertrand's sp_foreachdb is a much better piece of code that doesn't skip databases, etc.

edited Sep 7 at 12:26

answered Sep 7 at 12:10

sp_BlitzErik

19.5k1161101

add a commentÂ |Â

up vote
4
down vote

If you look at the sp_helptext entry for sp_MSforeachdb, or its weird friend sp_MSforeachtable

EXEC sys.sp_helptext @objname = N'sp_MSforeachdb';
EXEC sys.sp_helptext @objname = N'sp_MSforeachtable';

You'll see they're both just wrappers for sp_MSforeach_worker:

EXEC sys.sp_helptext @objname = N'sp_MSforeach_worker';

All they do is build up a valid <list of things>, but they don't actually loop over them in a meaningful way.

At any rate, Aaron Bertrand's sp_foreachdb is a much better piece of code that doesn't skip databases, etc.

edited Sep 7 at 12:26

answered Sep 7 at 12:10

sp_BlitzErik

19.5k1161101

add a commentÂ |Â

up vote
4
down vote

If you look at the sp_helptext entry for sp_MSforeachdb, or its weird friend sp_MSforeachtable

EXEC sys.sp_helptext @objname = N'sp_MSforeachdb';
EXEC sys.sp_helptext @objname = N'sp_MSforeachtable';

You'll see they're both just wrappers for sp_MSforeach_worker:

EXEC sys.sp_helptext @objname = N'sp_MSforeach_worker';

All they do is build up a valid <list of things>, but they don't actually loop over them in a meaningful way.

At any rate, Aaron Bertrand's sp_foreachdb is a much better piece of code that doesn't skip databases, etc.

edited Sep 7 at 12:26

answered Sep 7 at 12:10

sp_BlitzErik

19.5k1161101

If you look at the sp_helptext entry for sp_MSforeachdb, or its weird friend sp_MSforeachtable

EXEC sys.sp_helptext @objname = N'sp_MSforeachdb';
EXEC sys.sp_helptext @objname = N'sp_MSforeachtable';

You'll see they're both just wrappers for sp_MSforeach_worker:

EXEC sys.sp_helptext @objname = N'sp_MSforeach_worker';

All they do is build up a valid <list of things>, but they don't actually loop over them in a meaningful way.

At any rate, Aaron Bertrand's sp_foreachdb is a much better piece of code that doesn't skip databases, etc.

edited Sep 7 at 12:26

answered Sep 7 at 12:10

sp_BlitzErik

19.5k1161101

edited Sep 7 at 12:26

answered Sep 7 at 12:10

sp_BlitzErik

19.5k1161101

answered Sep 7 at 12:10

sp_BlitzErik

19.5k1161101

answered Sep 7 at 12:10

sp_BlitzErik

19.5k1161101

add a commentÂ |Â

up vote
2
down vote

If you run sp_helptext for the procedure, you will see that it creates a cursor with databases' names and then runs sp_MSforeach_worker.
Comment from sp_MSforeach_worker:

This is the worker proc for all of the "for each" type procs. Its
function is to read the next replacement name from the cursor (which
returns only a single name), plug it into the replacement locations
for the commands, and execute them. It assumes the cursor
"hCForEach***" has already been opened by its caller. worker_type
is a parameter that indicates whether we call this for a database (1)
or for a table (0)

So, sp_msforeachdb doesn't run a query on each database but runs a query with replaced '?' with database name for each database from master.dbo.sysdatabases.

answered Sep 7 at 12:15

Denis Rubashkin

2357

add a commentÂ |Â

up vote
2
down vote

If you run sp_helptext for the procedure, you will see that it creates a cursor with databases' names and then runs sp_MSforeach_worker.
Comment from sp_MSforeach_worker:

This is the worker proc for all of the "for each" type procs. Its
function is to read the next replacement name from the cursor (which
returns only a single name), plug it into the replacement locations
for the commands, and execute them. It assumes the cursor
"hCForEach***" has already been opened by its caller. worker_type
is a parameter that indicates whether we call this for a database (1)
or for a table (0)

So, sp_msforeachdb doesn't run a query on each database but runs a query with replaced '?' with database name for each database from master.dbo.sysdatabases.

answered Sep 7 at 12:15

Denis Rubashkin

2357

add a commentÂ |Â

up vote
2
down vote

If you run sp_helptext for the procedure, you will see that it creates a cursor with databases' names and then runs sp_MSforeach_worker.
Comment from sp_MSforeach_worker:

This is the worker proc for all of the "for each" type procs. Its
function is to read the next replacement name from the cursor (which
returns only a single name), plug it into the replacement locations
for the commands, and execute them. It assumes the cursor
"hCForEach***" has already been opened by its caller. worker_type
is a parameter that indicates whether we call this for a database (1)
or for a table (0)

So, sp_msforeachdb doesn't run a query on each database but runs a query with replaced '?' with database name for each database from master.dbo.sysdatabases.

answered Sep 7 at 12:15

Denis Rubashkin

2357

If you run sp_helptext for the procedure, you will see that it creates a cursor with databases' names and then runs sp_MSforeach_worker.
Comment from sp_MSforeach_worker:

This is the worker proc for all of the "for each" type procs. Its
function is to read the next replacement name from the cursor (which
returns only a single name), plug it into the replacement locations
for the commands, and execute them. It assumes the cursor
"hCForEach***" has already been opened by its caller. worker_type
is a parameter that indicates whether we call this for a database (1)
or for a table (0)

So, sp_msforeachdb doesn't run a query on each database but runs a query with replaced '?' with database name for each database from master.dbo.sysdatabases.

answered Sep 7 at 12:15

Denis Rubashkin

2357

answered Sep 7 at 12:15

Denis Rubashkin

2357

answered Sep 7 at 12:15

Denis Rubashkin

2357

answered Sep 7 at 12:15

Denis Rubashkin

2357

add a commentÂ |Â

draft saved

draft discarded

draft saved

draft discarded

Post as a guest

Name

Search This Blog

Iyfjky