• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1278
  • Last Modified:

sp_msforeachtable runs against one DB but not another, same server...

On the same server, this runs fine against one database, but gives the msg below on the other.  What is a possible reason for this?  Shouldn't it run on both because the procs are in the master database?

sp_msforeachtable 'execute sp_spaceused @objname = ''?'' '

Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'sp_msforeachtable'.
  • 2
2 Solutions
Scott PletcherSenior DBACommented:
Is it the same login/user running both?
Just for fun. Try "prefixing" the call;

master.dbo.sp_msforeachtable 'execute sp_spaceused @objname = ''?'' '

If this does not work, try asking for the Object ID. If this call returns a value it should be possible to call it...
SELECT OBJECT_ID('master.dbo.sp_msforeachtable')
This returns 1099150961 at my SQL 2000 server
donnatroniousAuthor Commented:

master.dbo.sp_msforeachtable 'execute sp_spaceused @objname = ''?'' ' gives no output on any of my databases, just says command completed successfully.

SELECT OBJECT_ID('master.dbo.sp_msforeachtable') gives -776307597 for all my databases

The first  test was a deadend, sorry about that.
The other test should indicate that the SP exists and should be possible to call.

The only hints about this problem I have found was a reference to a problem related to case sensitive installations, but the article seemed to not be for SQL Servers. It would be interesting to check anyhow to see if this (incorrect CapiTAlization) was the problem (seems like a longshot)...

Try this instead;

sp_MSforeachtable 'execute sp_spaceused @objname = ''?'' '

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now