Results 1 to 13 of 13
  1. #1
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10

    Unanswered: Querying sysobjects for tables in a single schema

    I think this should be a dead easy one.

    SQL Server 200.

    Can I query to find all the tables in a single schema? I can't for the life of me find this one out!

    I'm currently using a dreaded cursor with an exists test to get the results I want but there is surely an easier way!

    Cheers,
    G
    George
    Home | Blog

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    As soon as I posted it occured to me; it's owners in 2000, not schemas, right? (which might be why I can't find what I need!)
    George
    Home | Blog

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Yep

    sysobjects.uid = sysusers.uid
    George
    Home | Blog

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    This post adds nothing to the discussion at hand, but is meant only to block the imminent "Connect four" that the OP is apparently intending. ;-)

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    You don't know how tempted I am to delete your post and complete a connect 4 now
    George
    Home | Blog

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by MCrowley
    This post adds nothing to the discussion at hand, but is meant only to block the imminent "Connect four" that the OP is apparently intending. ;-)
    There wasn't much to the discussion at hand to start with... One person "talking to themselves" does not constitute a discussion in my universe!

    -PatP

  7. #7
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    should we tell the youngster to use INFORMATION_SCHEMA.TABLES instead of the system tables?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  8. #8
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Knowing this youngster, he is probably doing this on SQL 6.5. Let him rewrite everything when he gets to SQL 2005 ;-).

  9. #9
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    but he states clearly that he is on SQL 200, which is a version that must be so old I have never heard of it before.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Not at all, I'm working on SQL 9.00.3042.00 now (see SELECT @@version), so SQL 200 must be really, really new!

    -PatP

  11. #11
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I have never used information_schema; I was brought up using the sys tables and when it comes to 2005 the sys schema
    George
    Home | Blog

  12. #12
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    everyone always says to use information_schema views instead of the sys views. I don't see much point.

    If it's for portability, there's not much point because each vendor's implementation of information_schema is different in places. and many don't support them at all. oracle for example.

    if it's for adherence to ANSI standards, well, ok. but none of the vendors adhere to the standard. so what's the point?

    sometimes what you need is only in sys.* - so what do you do then? use a hybrid of joins between system tables and information_schema? yuck.

    besides, real portability of any real world database application from one vendor to another is a dream. I certainly have never come across one that wouldn't have needed lots of tweaking. Generally the stickiest part of such apps is the DBMS as it's at the bottom of the stack.

  13. #13
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Quote Originally Posted by Thrasymachus
    but he states clearly that he is on SQL 200, which is a version that must be so old I have never heard of it before.
    Next he'll be asking what "sybsystemprocs" is.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •