3

I'm facing strange behavior.

Here's how to reproduce: in an instance with multiple databases (SQL Server 2016 in my case), create that stored procedure (in master in my case):

create procedure dbo.Strange WITH EXECUTE AS owner
as
begin
    exec sp_MSforeachdb 'select''?''';
end;

Then call that stored procedure with:

Exec master.dbo.Strange

As you will see, it will only return data for some of the databases but not all (3 out of 11 in my case).

What is happening?

1
  • Can you tell us the names of the databases that work, and the ones that don't? Apr 21, 2017 at 15:18

1 Answer 1

13

sp_MSforeachdb has lots of known issues.

If you follow the link, you'll get to a post by Aaron Bertrand that details them, and also provides an alternate solution.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.