none
sp_MSForEachDB RRS feed

  • Question

  • Hello,
    I know that sp_MSForEachDB is not being supported, but I am wondering if someone would help me find out if I can use sp_MSForEachTable within sp_MSForEachDB.

    The code below does not work, and I am wondering if the code is feasible.
    Error Message - "cursor hCForEach already exist"

    declare @var varchar(1000)
    set @var =
    'EXEC sp_MSForEachTable @command1='INSERT INTO #tblResults2
               ([name],[rows],[reserved],[data],[index_size],[unused])
              EXEC sp_spaceused ''?''''

    EXEC sp_MSForEachDB @command1=@var


     

    Tuesday, May 9, 2006 4:19 AM

Answers

  • You will be better off writing your own code to loop through the databases and tables. These system stored procedures are not meant for general consumption so it will not work for all scenarios. It is intended for internal usage only.
    Wednesday, May 10, 2006 1:28 AM

All replies

  • Hi,

    Seems that they share a common gerneric procedure which open a cursor, though nested cursors with the same name are not supported. So you could either change the procedures (which is really not a good idea, or write a short cursor for this, which would be feasible in this situation.

    HTH; Jens Suessmeyer.

    ---
    http:/www.sqlserver2005.de
    ---
    Tuesday, May 9, 2006 5:44 AM
    Moderator
  • I wrote the following code which almost did the job.  For some reason, sp_MSForEachDB DID NOT loop through all the DB, but loop through the same DB that my session is in.  For example, if I am in AdventureWorks DB, and I have 9 databases created on my server, it would loop through all user tables on AdventureWorks, but repeat the insert 9 times instead of jumping to another DB.

    Any additional help is appreciated!
    -Lawrence

    EXEC sp_MSForEachDB @command1='
    EXEC sp_MSForEachTable @command1=''INSERT INTO law..LAW_tblSpaceUsed ([name],[rows],[reserved],[data],[index_size],[unused])
    EXEC sp_spaceused ''''%'''' '', @replacechar=''%'' '

     

    Tuesday, May 9, 2006 9:16 AM
  • You will be better off writing your own code to loop through the databases and tables. These system stored procedures are not meant for general consumption so it will not work for all scenarios. It is intended for internal usage only.
    Wednesday, May 10, 2006 1:28 AM