Invalid object name 'sys.spt_values'

  • 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

  • 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)

  • 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]

  • 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
  • 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

  • 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 )

  • 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

  • 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.

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

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