Invalid object name ‘master.dbo.spt_values’. (Microsoft SQL Server, Error: 208)

Hi Friends,

The last month has been a very busy month for all of us, and its not over yet. However, with the daily work, comes a few instances when we do encounter really interesting issues. End of the last month, I encountered an issue, that is really very interesting.

The issue that I am talking about is very uncommon; I have not seen many people facing it; although it is very easy to encounter this scenario. Also, this issue can be encountered in SQL Server 2005; although I have not tested this against SQL Server 2008.

I am sure all of us are aware that a system database called master exists and is the main database that is required for startup of the SQL Server. If we look into the master database, in SQL Server 2005, we would see that there are 5-6 system tables (some systems might have 5; some systems might have 6). But are we aware of what these tables contain, and what if these tables go missing?

You might be thinking - "What! Missing system tables; that too from the master database!" Believe me, its not very uncommon to have the system tables going missing. Right-Click on any of these tables, and choose delete... That's all about it...

Note: However, even after having a thorough understanding of this blogpost, I would recommend all users NOT to try this on any SQL Server instance...

There are several reasons why all or some of the system tables might go missing. An improper database upgrade from SQL Server 2000 can cause some or all of these tables go missing. Also, user activity (right-click and delete) can cause these tables go missing. In case its user activity that deleted these tables, an inspection of the C2 Audit Traces can reveal who did that and when. The default traces also can reveal this information; however, since the default traces are deleted periodically, there is a high probability that the information might get lost after a certain interval of time.

Now, the question remain is - what happens if these system tables go missing? Nothing much, only problem that will arise out of the missing dbo.spt_values table is that you can no longer right-click database objects and look at their properties. for example, in case you right-click database objects and choose properties, you would face an error message saying:

Cannot show requested dialog. (SqlMgmt)
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
Invalid object name 'master.dbo.spt_values'. (Microsoft SQL Server, Error: 208)

However, in case these system tables go missing, and you encounter the error message as mentioned above, don't panic; it can be repaired. In the <SQL Server Install Dir>\MSSQL\Install folder, there is a script file named u_tables.sql. Execute this script against the instance that has these tables missing; and there you go. The dbo.spt_monitor and dbo.spt_values table will get created and appropriate data will be filled in, there by the error message mentioned above will go away.

There is no default script to create the other 3-4 tables; however, these can be scripted out from any other instance and the script can be applied to create the rest of the missing tables. One of these table is a replication related table, that is supposed to have 3 rows of data; this data can also be imported from a good instance.

Hope you find this post useful!

Disclaimer: All information provided here is my personal opinion and is neither verified nor approved by Microsoft before it is published. All information, and code samples, if any, is provided "AS IS" with no warranties and confers no rights.

Comments (30)

  1. ANIL PATEL says:

    This will become very usefull for me.

    Thanks for his topic.

    Anil Patel


  2. Suhas De says:

    Hi Anil,

    Thanks for the comments. Glad to be able to help.



  3. CX says:

    Thanks for your explanation. It was exactly my problem, and I’ve solved it as you explain.

    Thank you very much.

  4. CX says:

    And your solution works in same way (well, in fact, u_tables.sql was in cd not in any hard disk folder).

  5. CX says:

    Seems that ‘Title’ is not posted: I want to say that my problem was in SQL Server 2008 and your solution works fine too.

  6. Suhas De says:

    Hi Cx,

    Thanks for the comments! It’s good to know that my efforts have been useful to you.

    The u_tables.sql script file should be located on the hard disk. On my machine it is located at "C:Program FilesMicrosoft SQL ServerMSSQL10.MSSQLSERVERMSSQLInstall". Not sure why this does not exist on your disk.

    Thanks, Suhas

  7. strider009 says:

    thank you. saved me a heap of time!

  8. Krishna Prakash says:

    Very useful this article and helped save time.

  9. Sunil Botadara says:

    This will become very usefull for me when i configure replication.

    Thanks for this topic.

    Sunil Botadara


  10. Heo says:

    really thank you for your post!!!!!!!

    i have been hard rime for more than 48 hours~

  11. JanK says:

    That fixed it with no trouble.  Thank you!

  12. Kira Kumar says:

    I was facing the exact problem….and got resolved with this solution…. Thanks a lot for this posting

  13. Mohan says:

    Excellent!!!. Even , I had the same problem.It has really fixed my problem. Good post buddy…

  14. Vankayala says:

    Thanks, it helped me.

  15. Pritesh kumar yadav says:

    Thanks..really helpful document!!!!!!!

  16. Steve says:

    You saved my day. Thanks

  17. Mike A says:

    This came in handy today! Thank you!

  18. Moo says:

    your article saved my ass today. Thanks so much!

  19. Naveen says:

    Thanks for this post. This worked well for me.

  20. Urvi says:

    Thanks a lot for this post. This fixed the problem. Although I don't seem to think the real reason behind this can be an improper upgrade or a user-activity ( atleast in my case its not for sure).

  21. Hima Nagisetty says:

    Thank you Suhas, this definitely helped me.

  22. vikas pathak says:

    I was facing same problem in .2012 I have resolved using this tips. Thank you

  23. Steve says:

    This does not work on SQL2012. I have the u_tables.sql script but running it does not fix the issue (in fact it also complains that spt_values does not exist).

  24. Axel says:

    You have the process of exporting and importing tables spt?

    Thanks for you Reply.

  25. John says:

    @ Steve (20 Dec 2013)

    Were you able to resolve this? I am having the same issue in SQL2014.

  26. Jo says:

    This is really awesome Man…got resolved using your suggestions  Keep going…. My wishes to you

  27. I never came across this issue but I am happy to be familiar with this issue and resolution. Thanks much !!

  28. Daniel Adeniji says:

    Thanks for sharing.



  29. Cesar Moreno says:

    Thanks a lot! I was looking for this fix for a long time, it helped me a lot.

Skip to main content