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:
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…”.
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.
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.
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.
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
Step 6: For the destination object we need to change it from [dbo].[Query] to the actual object name as shown below.
Enter the destination object as ‘dbo.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.
Step 8: As you can see all rows have been imported successfully.
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.
Next Steps
- Please do proper testing before implementing this solution in a production environment.
- Explore more tips on SQL Server Database Administration.
