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

Posted on 2007-08-10
Last Modified: 2008-01-09
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'.
Question by:donnatronious
    LVL 68

    Assisted Solution

    Is it the same login/user running both?
    LVL 12

    Accepted Solution

    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

    Author Comment


    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

    LVL 12

    Expert Comment

    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

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Join & Write a Comment

    Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
    PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
    Via a live example, show how to shrink a transaction log file down to a reasonable size.
    Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now