Professional Web Applications Themes

Object property question - Microsoft SQL / MS SQL Server

SQL Server 2000 If I run the following query using Query yser from the 'testdata' database the OBJECTPROPERTY of user tables is 1. But if I run it from another database (which is what I want to do) user tables (and some others) return a null value. select OBJECTPROPERTY(id, N'IsUserTable') from testdata..sysobjects How can I identify user tables from a query in a different database? Thanks for any help. Adrian...

  1. #1

    Default Object property question

    SQL Server 2000

    If I run the following query using Query yser from the 'testdata'
    database the OBJECTPROPERTY of user tables is 1. But if I run it from
    another database (which is what I want to do) user tables (and some others)
    return a null value.

    select OBJECTPROPERTY(id, N'IsUserTable')
    from testdata..sysobjects

    How can I identify user tables from a query in a different database?

    Thanks for any help.

    Adrian


    Adrian Guest

  2. #2

    Default Re: Object property question

    OBJECTPROPERTY will refer to the current database, so you need to be in the
    scope of the target database to rely on its results.




    "Adrian" <nospamhotmail.com> wrote in message
    news:wOVOa.6272$g92.49472735news-text.cableinet.net...
    > SQL Server 2000
    >
    > If I run the following query using Query yser from the 'testdata'
    > database the OBJECTPROPERTY of user tables is 1. But if I run it from
    > another database (which is what I want to do) user tables (and some
    others)
    > return a null value.
    >
    > select OBJECTPROPERTY(id, N'IsUserTable')
    > from testdata..sysobjects
    >
    > How can I identify user tables from a query in a different database?
    >
    > Thanks for any help.
    >
    > Adrian
    >
    >

    Aaron Bertrand - MVP Guest

  3. #3

    Default Re: Object property question

    Aaron,

    Thanks. Is there any other way of checking (from another database) that a
    user is deleting a user table?

    Adrian.

    "Aaron Bertrand - MVP" <aaronTRASHaspfaq.com> wrote in message
    news:ebVNIhiRDHA.2144TK2MSFTNGP11.phx.gbl...
    > OBJECTPROPERTY will refer to the current database, so you need to be in
    the
    > scope of the target database to rely on its results.
    >
    >
    >
    >
    > "Adrian" <nospamhotmail.com> wrote in message
    > news:wOVOa.6272$g92.49472735news-text.cableinet.net...
    > > SQL Server 2000
    > >
    > > If I run the following query using Query yser from the 'testdata'
    > > database the OBJECTPROPERTY of user tables is 1. But if I run it from
    > > another database (which is what I want to do) user tables (and some
    > others)
    > > return a null value.
    > >
    > > select OBJECTPROPERTY(id, N'IsUserTable')
    > > from testdata..sysobjects
    > >
    > > How can I identify user tables from a query in a different database?
    > >
    > > Thanks for any help.
    > >
    > > Adrian
    > >
    > >
    >
    >

    Adrian Guest

  4. #4

    Default Re: Object property question

    > Thanks. Is there any other way of checking (from another database) that a
    > user is deleting a user table?
    As opposed to, what, a system table?


    Aaron Bertrand - MVP Guest

  5. #5

    Default Re: Object property question

    In other words, if they are using a DROP TABLE command, and it is *not* a
    user table, they should see something like:

    Server: Msg 3708, Level 16, State 1, Line 2
    Cannot drop the table 'sysobjects' because it is a system table.




    "Adrian" <nospamhotmail.com> wrote in message
    news:o2WOa.6295$gm2.49624118news-text.cableinet.net...
    > Aaron,
    >
    > Thanks. Is there any other way of checking (from another database) that a
    > user is deleting a user table?
    >
    > Adrian.

    Aaron Bertrand - MVP Guest

  6. #6

    Default Re: Object property question

    > if exists (select * from testdata..sysobjects where id = object_id(N'
    > testdata..testtable' and type='u')
    Or maybe that's xtype, I forget... I tend to shy away from accessing system
    tables directly, because their behavior may change, and they are potentially
    going to disappear altogether someday...


    Aaron Bertrand - MVP Guest

  7. #7

    Default Re: Object property question

    Aaron,
    >>> So why are you bothering?
    I was beginning to ask myself that question ; )

    I am fairly new to all this and was revising some existing scripts which
    were testing if the table was a user table as my example, but this does seem
    unnecessary for what I am doing.

    Anyway, I am very grateful for your help.

    Adrian.



    "Aaron Bertrand - MVP" <aaronTRASHaspfaq.com> wrote in message
    news:ONMLnBjRDHA.1072TK2MSFTNGP10.phx.gbl...
    > > if exists (select * from testdata..sysobjects where id = object_id(N'
    > > testdata..testtable') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
    >
    > or
    >
    > if exists (select * from testdata..sysobjects where id = object_id(N'
    > testdata..testtable' and type='u')
    >
    > > In fact I probably do not HAVE to check that it is a User table before
    > > dropping it as the name of the tavble cannot be decided by the user,
    >
    > So why are you bothering?
    >
    >

    Adrian Guest

Similar Threads

  1. Replies: 0
    Last Post: November 13th, 11:04 AM
  2. Passing an object as a property???
    By Henri in forum ASP.NET Building Controls
    Replies: 1
    Last Post: May 18th, 05:12 PM
  3. Object property with different return types
    By Simbad in forum ASP.NET Building Controls
    Replies: 1
    Last Post: January 18th, 01:36 PM
  4. Access to Session Object Property
    By Dorsa in forum ASP.NET Security
    Replies: 1
    Last Post: December 17th, 06:45 PM
  5. DefinedSize property of Field object
    By Lorenzo Bolognini in forum ASP
    Replies: 3
    Last Post: September 27th, 03:15 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139