0

Having a problem with a particular table. Was using Kimberly Tripp's script to rebuild indexes based on fragmentation level. The job failed with this error:

Msg 2501, Sev 16: Could not find a table or object named 'CMS_InfoObjects6'. Check sysobjects. [SQLSTATE 42S02]

I checked sysobjects and see the tablename listed. select object_name(2137058649) returns CMS_InfoObjects6. select object_id('CMS_InfoObjects6') returns null. select object_id(object_name(2137058649)) returns null as well.

I can see the table in EM and can select * from it in QA when using a fully qualified name (owner is not dbo).

dbcc checkdb returns no errors.

declare @name varchar(500) set @name = object_name(2137058649) dbcc checktable (@name)

returns "Could not find a table or object named 'CMS_InfoObjects6'. Check sysobjects."

I'm confused.

2
  • Do you mind posting a link to the script? Also what version is the script meant for? I know I wrote a script to check fragmentation levels that won't work in 2000 since it uses features that weren't available in that version.
    – Shane
    Aug 10, 2010 at 17:24
  • The script is linked to from sqlskills.com/BLOGS/KIMBERLY/post/… in the answer to the 3rd question on that page.
    – Shaun
    Aug 10, 2010 at 17:37

1 Answer 1

3

In your variable, you're not including the schema. If the object isn't in the default schema (like in this case, where you said it wasn't dbo) then you have to pass in the fully qualified name to DBCC CHECKTABLE. Check out the example scripts on the bottom of the Books Online page for that command:

http://msdn.microsoft.com/en-us/library/ms174338.aspx

You'll need to modify Kimberly's script to include the schema. (It's funny, she's teaching a class in front of me as we speak, hahaha.)

2
  • Ah..Ok.. I thought that might be the problem. And Using the schema name in the SELECT OBJECT_ID query works. What threw me was two things: 1) the select object_id(object_name(2137058649)) query returning null and 2) I have another db that also has non-dbo owned tables and the job didn't fail on those. I guess it didn't process those databases before this one. Thanks so much everyone.. Thanks for the prompt responses!
    – Shaun
    Aug 10, 2010 at 17:31
  • No problem! If it's the right answer, hit the Accept Answer thing here so it'll close the question. Thanks!
    – Brent Ozar
    Aug 10, 2010 at 17:51

You must log in to answer this question.