Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Fix invalid object name master.dbo.spt_values when viewing SQL Server database properties


By:   |   Read Comments (2)   |   Related Tips: More > SQL Server Management Studio

Problem

While launching the database property page of a database in SQL Server Management Studio, I received this error: Invalid object name 'master.dbo.spt_values'. What does this mean? How did this error occur? How can I fix this error? In this tip we will look at a workaround to fix this error.

Solution

I came across this issue when I was trying to launch the database property page for a database in SQL Server Management Studio (SSMS). If you right click on a database in SSMS you will get the following error message:

Error Shot

From the error it looks like object dbo.sp_values is not found in the master database. I did some Google searches and found this as an undocumented system table, which contains various data items used by a Microsoft system stored procedures in the master database. Many people and some experts suggested running a SQL script named u_table.sql, which ships with SQL Server and is located on the drive where you installed SQL Server. My SQL Server program files are installed on the C: drive, so this script is located in "C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Install". I executed this script, but this didn't solve the issue.

Since the issue remained, I found a work-around to fix this issue. I decided to import the same object from another server which was running the same SQL Server version and edition. This will not create a system view, but it will create a user object with the same name and can be workaround to fix this issue. This solution worked and fixed my issue, then I was able to launch the database property page in SSMS.

Step 1: There are multiple ways to create this object from other servers. You can generate a script or you can use the Import Export Wizard. I used the Import Export Wizard to import this object from a similar server. Go to the instance where you are facing this issue and right click on the master database, choose "Tasks" and then click "Import Data...".

Launch Import Export Wizard

Step 2: The welcome page for the import and export page will appear. Click Next to enter source details of the server from where we want to import this object and click Next.

Enter source details in Import Export Wizard

Step 3: Similarly, enter destination details where you want this object "master.dbo.spt_values" to be created and click Next. We want this table to be created in the master database.

Enter edestination details

Step 4: You will get a screen like below. Select the second option "Write a query to specify the data to transfer" and click Next.

No user table visible

Step 5: Since object 'master.dbo.spt_values' is a system object, we need to import this by using a T-SQL query. Enter the following query and click Next.

SELECT * FROM master.dbo.spt_values

Source Query

Step 6: For the destination object we need to change it from [dbo].[Query] to the actual object name as shown below.

Mapping

Enter the destination object as 'dbo.spt_values'.

name change to spt_values

Step 7: Once you renamed the destination object name the in above window, click "Preview...", this will show you the data from the source object to be imported. Click Next to do the actual import.

Preview

Step 8: As you can see all rows have been imported successfully.

Execution Successful

Step 9: Now go ahead and launch the database property page again to check and verify whether this applied change worked to fix this issue or not. As we can see the database property page is displaying correctly, so this fixed the issue.

Launch_properties
Next Steps


Last Update:






About the author
MSSQLTips author Manvendra Singh Manvendra Singh has over 5 years of experience with SQL Server and has focused on Database Mirroring, Replication, Log Shipping, etc.

View all my tips
Related Resources





More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Thursday, April 06, 2017 - 7:36:59 AM - Niraj Sevalkar Back To Top

I too faced same issue, by using this article i have resolved my issue, Helpful post.

Thank you 

 


Tuesday, July 05, 2016 - 10:32:52 AM - dmoody Back To Top

 This did indeed solve the problem, but I am left with an unfortunate side affect. The data exported from the hidden table in my source database is now showing as a regular user table in my destination database. I am wondering if there is anyway to make sure all table properties are carried over as well so that the table doesn't show as a user table.

 


Learn more about SQL Server tools