How to get the table name from a objectID?

• Eric B. Webb

  Valued Member

  Points: 68

  More actions

  October 12, 2016 at 1:10 pm

  #1906187

  Try this on for size. It will give you the table name base on the object id in the where clause.

  SELECT *

  FROM sys.objects

  WHERE object_id = 632864

  GO

  --Here's the reverse - to get the Object Id of a known table name. "Department" is the name of the table

  SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].department')

  GO

  You can also use the following code to see if a table exist.

  -- this is also good if you need to check is a table exist in SQL

  IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].department') AND type in (N'U'))

  print 'expression is False'

  ELSE

  PRINT 'expression is TRUE.' ;

• Eric M Russell

  SSC Guru

  Points: 125255

  More actions

  October 12, 2016 at 1:22 pm

  #1906192

  It sounds like you have data corruption, but you can also try the following to quickly search for an object_id across a multiple databases.

  exec sp_MsForEachDB

  '

  USE ?;

  print ''Searching ... ?''

  print ''Found: ''+object_name( 632864 );

  ';

  "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

• Bill Talada

  SSChampion

  Points: 11956

  More actions

  October 12, 2016 at 1:37 pm

  #1906197

  Not sure if you need the structure or the data; ideally you want both of course. I haven't lost data since a sql server 6.5 bad service pack but here is what I did then:

  I've had some success rescuing data by using the non-clustered indexes to retrieve it when the clustered index tree is toast. You just need to be smart enough to write the correct WHERE clause and maybe jump over data pages that are bad. Works in a pinch.

• Luis Cazares

  SSC Guru

  Points: 183686

  More actions

  October 12, 2016 at 2:46 pm

  #1906214

  I really hope the OP didn't depend on the last replies. Not because they're bad, but because it's been over 3 years now.

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• MMartin1

  One Orange Chip

  Points: 27655

  More actions

  October 13, 2016 at 5:45 pm

  #1906485

  Luis Cazares (10/12/2016)

  ---

  I really hope the OP didn't depend on the last replies. Not because they're bad, but because it's been over 3 years now.

  I also hope the OP migrated away from SQL Server 2000 and discovered what lead to the massive corruption in the years since.

  Though I am curious about one thing, would having log shipping make the data somehow accessible still if one finds themselves in this situation?

  ----------------------------------------------------