Today i encounter a issue with my instance as i am using the table SPT_VALUES from the master database available in our Sql Server, so when i tried to fetch some records from master..spt_values then it shows me error…
Here is a example of query and the error thrown by SQL is..
SELECT * FROM orders WHERE order_id in (SELECT number FROM master..spt_values WHERE type='P')
and Error is..
Msg 208, Level 16, State 1, Line 1
Invalid object name 'master..spt_values'.
So it is very uncommon thing to me to loose table from Master Database in Sql Server 2005. But i found that it is not a very uncommon thing because if you can right click on the table present in Master Database and choose the ‘DELETE’ option then table would be deleted from your server.
Now what happen next when tables get deleted from your Master Database, there are some information which stored in Tables in Master Database…
So i googled a lot about this and here is the solution…
There is a script file named “u_tables.sql” in “installation directory >MSSQL>Install” folder .
Execute this script against the instance that has these missing tables, and you will get the tables again.
But this script has limitations because its only able to recreate /reproduce only two System Tables “dbo.spt_monitor and dbo.spt_values” .
So in this way you can get your tables back in Master Database..But it is highly recommended that you don’t delete tables present in Master Database.