locked
find database and table from object id RRS feed

  • Question

  • I have an object id.
    I want to find the database name and table name corresponding to it.
    Can anyone please help me?
    Thursday, June 25, 2009 1:12 AM

Answers

  • The Object_ID exists in the context of the DB.  For example,  DB1 might have Object_ID 309576141 that is a table, while another database on the same instance of SQL has and object_Id 309576141 that is a function. 

    The Scope of the Object_ID value is limited to the Database.

    To get the object name, you use
    select Object_Name(<Object_ID>)

    like this:

    select Object_Name(309576141)
    PAC
    • Marked as answer by Kalman Toth Thursday, June 25, 2009 1:46 AM
    Thursday, June 25, 2009 1:18 AM
  • SELECT name
    FROM sys.objects
    WHERE object_id = @objectid


    Will give you the table name, given the table object id.  But object ids aren't unique instance-wide; they apply only to a particular database, which means you can't uniquely determine the db for a given id.  The most you can do is iterate across all databases, and see which have the given id (it may be more than one)


    Michael Asher
    • Marked as answer by ManjotK Thursday, June 25, 2009 1:56 AM
    Thursday, June 25, 2009 1:18 AM

All replies

  • The Object_ID exists in the context of the DB.  For example,  DB1 might have Object_ID 309576141 that is a table, while another database on the same instance of SQL has and object_Id 309576141 that is a function. 

    The Scope of the Object_ID value is limited to the Database.

    To get the object name, you use
    select Object_Name(<Object_ID>)

    like this:

    select Object_Name(309576141)
    PAC
    • Marked as answer by Kalman Toth Thursday, June 25, 2009 1:46 AM
    Thursday, June 25, 2009 1:18 AM
  • SELECT name
    FROM sys.objects
    WHERE object_id = @objectid


    Will give you the table name, given the table object id.  But object ids aren't unique instance-wide; they apply only to a particular database, which means you can't uniquely determine the db for a given id.  The most you can do is iterate across all databases, and see which have the given id (it may be more than one)


    Michael Asher
    • Marked as answer by ManjotK Thursday, June 25, 2009 1:56 AM
    Thursday, June 25, 2009 1:18 AM
  • but for that i have to know the database isnt it?
    I hv used:

    sp_msforeachdb 'select * from sysobjects where......'

    Is this not an efficient way?
    Thursday, June 25, 2009 1:45 AM
  • Manjotk,

    That's an efficient method, but the problem is you may find the same object id in multiple databases.  In that case, you'd have to arbitrarily choose which one is the 'correct' db.

    Michael Asher
    Thursday, June 25, 2009 1:49 AM
  • Agree with Michael.  The real question is "why?".  Is there are problem that you are trying to solve that we might help you do an easier way. It is not a commonplace thing to have an object id in hand without any knowledge of the database it comes from...  The only thing I can think of is an error message that you are trying to track down, in which case the foreachdb query is a good idea.

    The likelihood of having duplicate object_id values is probably kind low, but it is far too likely to use the object_id as a guaranteed unique value.
    http://drsql.spaces.msn.com
    Thursday, June 25, 2009 3:03 AM