Results 1 to 10 of 10
  1. #1
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716

    Unanswered: Point of Information - checking for existence of a table in another database...

    Hi all,

    While cleaning up some code, I ran across the following statement in a stored proc - the purpose of which is to determine if a table exists in the local database:
    Code:
    SELECT * FROM dbo.sysobjects where id = object_id(N'[dbo].[XML_PRINTDATE]') and OBJECTPROPERTY(id, N'IsUserTable') = 1
    of course I removed it from the IF just for testing purposes, but my quandry is this...
    Why chose that select (converting table name to object ID) rather than just doing THIS:
    Code:
    SELECT * FROM dbo.sysobjects where name = N'XML_PRINTDATE' and OBJECTPROPERTY(id, N'IsUserTable') = 1
    I first thought it was to gain access to the "id" column value (and that may yet be the purpose of it), but the second code seems to work just peachy (I assume because the id column is present in the sysobjects table itself).

    A follow-on question is this:
    When I try to do the same check from another server (i.e.
    Code:
    SELECT * FROM APRECEIVE1.DailyProd.dbo.sysobjects where name = N'XML_PRINTDATE' and OBJECTPROPERTY(id, N'IsUserTable') = 1
    ) it of course fails because OBJECTPROPERTY only looks for the id on the local database.

    So, do I CARE if it is a user table? (I am reasonably sure it is, of course) and if so, is there a way to check on the remote server for the object type?

    Bottom line is I Think I can just simplify things and check for the object name on the remote server, but just don't want to take away any "warm fuzzy feeling" generated by the original stored proc, if such a warm fuzzy is of any benefit (though don't get me started on the relativity of warm fuzzies, I wrote my Thesis on that )
    Last edited by TallCowboy0614; 02-22-05 at 16:50.
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Checking on the indexes on sysobjects, there are two that seem to apply. There is one on ID (PK Clustered), and one on (name, uid). Going by id would seem to use just the PK index. Keep in mind, that you can have several tables all with the same name, provided that different users own them. This may be another reason the former programmers went with this approach.

    As for the other question, the query for OBJECTPROPERTY(id, N'IsUserTable') will look to the local database every time for the results. You can't do that from a remote database.

  3. #3
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    Hmmm...that's an interesting thought...I didn't consider the different user angle. I'll have to play with that...I betcha you are right though, as far as the motivation. Good catch! Not likely to be a dup in my situation, but ya gotta respect the defensive programming regardless.

    OK, for testing purposes, I created two tables with identical names, and sho'nuff, the UID is 1 and 5. I assume the dbo is UID 1...but is that always the case? I could look for name='xxx' and UID=1 if so, otherwise I guess I will have to get UID.

    THAT being the case, I think I can accomplish the original goal even across servers by doing THIS
    Code:
    select * from APRECEIVE1.DailyProd.dbo.sysobjects where name = 'XML_PRINTDATE' and uid = USER_ID('dbo')
    which SEEMS to be working for me.

    Can you think of any pebbles I may trip over later if I go down this path?
    Thanks much for the help!!! I love you guys...well...I mean in a manly, SQL selection kinda way
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    I think dbo always gets uid = 1. I can not think of any way to make dbo anything other than uid = 1. BUT, there is still a lot I have to learn about SQL Server. You can certainly have members of the db_owner role that are uid > 1, anyway. Not to mention members of db_ddladmin.

    Anyway, the one thing that jumps out at me about your code is the use of user_id('dbo'). This will use the local dbo's user id. To try it out, create a user who owns a table in the remote database, and supply his name in place of 'dbo' in your query. I bet you will get no results. At least, if that user does not happen to be in the local database with the same uid, anyway.

  5. #5
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    Dang! Just when I thought I was IN, they pull me back OUT... (well, SOMETHING like that anyway...). Yep, sho'nuff...it is as you said. Shoulda known that since there was no server/database reference on the USER_ID call...dang.
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  6. #6
    Join Date
    Jan 2005
    Location
    TempDb
    Posts
    228
    Code:
    Select * 
    From APRECEIVE1.DailyProd.dbo.sysobjects 
    Where name = 'XML_PRINTDATE' 
        And type = 'U'  -- User Table
    I love deadlines. I like the whooshing sound they make as they fly by. Douglas Adams

  7. #7
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    Thanks Max,

    I actually was about to post that very thing and do my victory dance, but realized it still doesn't protect me from the situation where I have multiple tables with the same name, but which are owned by separate users. However, I am rapidly reaching the end of my professional interest in this level of granularity *LOL* So I may compromise my principles and go for the middle ground of reasonableness (after all, we DO control the tables in this particular database, so I need only protect myself from my coworkers - yes, the most dangerous of adversaries *lol*)
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    That snippet of code appears to be copied more or less intact from the SQL-DMO generated code that does the same thing when Microsoft creates a script. That syntax is considered more portable (by the SQL Server gods) than the equivalent expression involving system tables.

    -PatP

  9. #9
    Join Date
    Jan 2005
    Location
    TempDb
    Posts
    228
    Although I have no bones about plaigarism, unless it is costing somebody money, that was not copied from anywhere. Also, that same syntax will work on next version of SQL Server AND on the original version of SQL Server for OS/2.

    I hesitated posting it because it relies on the SQL Server system schema not changing, but it should last about 10 years or until SQL Server 2010, whichever comes first.

    Finally, wrt ownership, which owners would you want to exclude/include? That seemed like the bigger challenge - deciding which ones, not actually excluding them. That is because nothing ensures UserA in ServerA.DatabaseA == UserA in ServerB.DatabaseA.
    I love deadlines. I like the whooshing sound they make as they fly by. Douglas Adams

  10. #10
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    Max, my primary concern is just making sure I am checking for the existence of the table I THINK I am checking for. I know that my target table has an owner of dbo, so that would be the one I'm seeking. I know in my particular instance, that it is pretty unlikely that anyone would create an identically-named table, so this situation is primarily about wrestling with my conscience (never an easy task in my life ) over the idea of "that should work most of the time" and that of "I can rest assured that this will work every time". In short, the proverbial and ageless wrestling match between developers and management
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

Posting Permissions

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