Post reply

Need to return the result of dynamic execution to a variable

  • April 8, 2008 at 8:05 pm

    #800095

    Matt Miller (4/8/2008)

    Jeff - which ones? sys.all_objects is context-sensitive, as is all_columns, all_views, syscomments, sysobjects. I haven't tested them all - but I have yet to find one that shows all objects in every DB.

    I knew it! I just bloody well knew it! I knew that if I kept working with Oracle enough (not my choice), that I'd someday get some of the things between Oracle and SQL Server confused...

    You're correct... all the "sys.ALL_" system views and similar views are schema scope sensitive in SQL Server. In Oracle, many are not.

    My most sincere apologies...

    However, couldn't you use the undocumented sp_MSForEachDB along the sys.All_Objects view to populate a Temp table with all the information?

    --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)

  • April 8, 2008 at 8:39 pm

    #800108

    Jeff Moden (4/8/2008)

    Matt Miller (4/8/2008)

    Jeff - which ones? sys.all_objects is context-sensitive, as is all_columns, all_views, syscomments, sysobjects. I haven't tested them all - but I have yet to find one that shows all objects in every DB.

    I knew it! I just bloody well knew it! I knew that if I kept working with Oracle enough (not my choice), that I'd someday get some of the things between Oracle and SQL Server confused...

    You're correct... all the "sys.ALL_" system views and similar views are schema scope sensitive in SQL Server. In Oracle, many are not.

    My most sincere apologies...

    However, couldn't you use the undocumented sp_MSForEachDB along the sys.All_Objects view to populate a Temp table with all the information?

    Absolutely - that's about what I was thinking the "high volume" version should be.

    And I know how you feel - sorry that Oracle is currently rotting your brain:).....

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Viewing 2 posts - 16 through 16 (of 16 total)

You must be logged in to reply to this topic. Login to reply