Home Forums Article Discussions Article Discussions by Author Discuss content posted by Chris Morton Loop Through every table in a DB RE: Loop Through every table in a DB

  • January 21, 2009 at 1:05 pm

    #930658

    RBarryYoung (1/21/2009)

    toniupstny (1/21/2009)

    The ms_foreach.. proc is an unofficial method and can be removed at the will of Bill et al. There are also cursors in the ms_foreach... procs, though unless you get to humongous numbers of tables and lengthy statements, they might be okay.

    The solutions with the Loops are most likely better than the cursor - no?

    Toni

    No. Loops and cursors are pretty much the same things with similar problems with respect to performance.

    And yes, "sp_MSforeachtable" is undocumented, but it does work and is still in 2000, 2005 and 2008. And it has more features than any of these.

    And by SQL 2005, you do not need any of the cursors/loop techniques anymore.

    To further that, a good ol' "firehose" cursor is just as fast as a While Loop and uses about the same number of resources.

    So far as using undocumented features goes because they might change it... I've got news for you... they change documented features without warning, as well. Take, for example, when they changed the privs on sp_MakeWebTask in 2k sp4... it caught a lot of people very flat footed and broke a lot of code. Heh... use what you can, can what you can't. 😉

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)