Invalid object name 'sys.spt_values'

Question

Invalid object name 'sys.spt_values'

Indianrock

SSC-Insane

Points: 20333
More actions
September 19, 2013 at 2:09 pm

#298944

Sql 2012 Enterprise installation. user databases are all fine. Managing objects in management studio doesn't work. We know an application wrote its tables into the master database by mistake. I found the utables.sql script which will recreate the master database views, but it fails because it can't find sys.spt_values
Invalid object name 'sys.spt_values' I have backups of master but was hoping this script would work.
create view spt_values as
select name collate database_default as name,
number,
type collate database_default as type,
low, high, status
from sys.spt_values
go

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 1

Indianrock (9/19/2013)
Sql 2012 Enterprise installation. user databases are all fine. Managing objects in management studio doesn't work. We know an application wrote its tables into the master database by mistake. I found the utables.sql script which will recreate the master database views, but it fails because it can't find sys.spt_values
Invalid object name 'sys.spt_values' I have backups of master but was hoping this script would work.
create view spt_values as
select name collate database_default as name,
number,
type collate database_default as type,
low, high, status
from sys.spt_values
go

I don't have 2k12 to confirm but try dbo.spt_values instead of sys.spt_values.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 2

Jeff Moden (9/19/2013)
I don't have 2k12 to confirm but try dbo.spt_values instead of sys.spt_values.

I just tried and that works as long as you're in [master]. (If not, you just have to make it [font="Courier New"]master.dbo.spt_values[/font])

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Honestly, if something's messed up master, I would recommend restoring it from the latest backup before whatever happened.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Indianrock SSC-Insane Points: 20333 More actions · Answer 4

dbo.spt_values doesn't exist, but even if it did can you create a view with the name matching the table it views? The only things not working are right clicking a table and selecting properties. right clicking anything else like a table seems to work. Also our Quest/Dell Spotlight tool won't monitor this instance for the same reason. I do have backups but on a cluster, restoring master would be a slightly big deal for me, not ever having restored master anywhere. I've removed all of the views/tables the accidental tool install put in master and since this is a production system would have to be done on Sunday probably. The u_tables script has this "-- Create a synonym spt_values in master pointing to spt_master in Resource DB, for backward compatibility" So how is this create view statement going to find spt_master in the resource DB?

create view spt_values as

select name collate database_default as name,

number,

type collate database_default as type,

low, high, status

from dbo.spt_values

Invalid object name 'dbo.spt_values'

-- sys.spt_values

go

Indianrock SSC-Insane Points: 20333 More actions · Answer 5

Restoring master from backup doesn't look all that daunting after a bit of reading. Now this is a two-node failover cluster so I'd want to make sure that the default instance in question comes back up on its normal node in single-user mode ( since I have the master backup set aside on that C drive for use with sqlcmd )

Indianrock SSC-Insane Points: 20333 More actions · Answer 6

Planning to restore master from backup, but that is probably a couple of weeks out. I've noticed that on our sql 2012 instances this view reveals 2,515 records. I can fairly easily either put those records into a table matching the spt_values structure ( in the master where its missing ) or put the data into "missingSpt" and create the spt_values view to look at that table. A bandaid until we can restore from master.

I have no idea what this could do

Indianrock SSC-Insane Points: 20333 More actions · Answer 7

Creating a temporary spt_values view of a temporary table containing the 2,515 rows worked. All of the functionality missing is now working. We still need to do the master restore. It sure would be interesting to know what "table" the spt_values view actually "views" I haven't been able to find that on other sql 2012 servers.