SQL Server 2014: Re-Create System view master.dbo.spt_values

Question

On my test SQL Server 2014 installation, I was "cleaning" the master database.

With the following command, I was checking which user objects there are:

SELECT
'DROP ' +
    CASE 
        WHEN [sys].[all_objects].type IN ('AF','FN','FS','FT','IF','TF') THEN 'FUNCTION '
    WHEN [sys].[all_objects].type IN ('D','C','F','PK','UQ') THEN 'CONSTRAINT '
    WHEN [sys].[all_objects].type IN ('IT','S','U') THEN 'TABLE '
    WHEN [sys].[all_objects].type IN ('P','PC','RF','X') THEN 'PROCEDURE '
    WHEN [sys].[all_objects].type IN ('TA','TR') THEN 'TRIGGER '
    WHEN [sys].[all_objects].type = 'R' THEN 'RULE '
    WHEN [sys].[all_objects].type = 'SN' THEN 'SYNONYM '
    WHEN [sys].[all_objects].type = 'TT' THEN 'TYPE '
    WHEN [sys].[all_objects].type = 'V' THEN 'VIEW '
END  +
    SCHEMA_NAME(sys.[all_objects].[schema_id]) + '.' + OBJECT_NAME(object_id) + '; ' as [Command], 
    OBJECT_NAME(object_id) as [ObjectName], 
    [sys].[all_objects].[type_desc] as [TypeDesc], 
    [sys].[all_objects].[type] as [Type], 
    SCHEMA_NAME(sys.[all_objects].[schema_id]) as [Schema]
FROM 
    sys.[all_objects] WITH (NOLOCK) 
WHERE SCHEMA_NAME(sys.[all_objects].[schema_id]) like '%dbo%'

One of the results was the view spt_values.

Command                 | ObjectName | TypeDesc | Type | Schema
------------------------|------------|----_-----|------|-------
DROP VIEW dbo.spt_values; spt_values   VIEW       V      dbo

As it was not one of the views I knew, I deleted it (along with other objects).

Later that day, I wanted to check the properties of a database in SSMS 2016 and got the following error:

After some searching, I found that I could recreate the missing view with the script u_tables.sql (which is in the SQL Server installation folder on your server). Information from here: https://ashishgilhotra.wordpress.com/tag/u_tables-sql/

The code in that script to create the view is the following:

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

EXEC sp_MS_marksystemobject 'spt_values'
go

grant select on spt_values to public
go

Already when looking at the code, I doubted that it would work, as there is no sys.spt_values table anywhere to be found.

As expected I get the error

Msg 208, Level 16, State 1, Procedure spt_values, Line 6
Invalid object name 'sys.spt_values'.

On my other server with SQL Server 2008 on it, there is a table master.dbo.spt_values (but no view)!

After some more searching, I found that I could just create a table with the same name.. Link here https://www.mssqltips.com/sqlservertip/3694/fix-invalid-object-name-masterdbosptvalues-when-viewing-sql-server-database-properties/

Now I create a table with the values from another SQL Server 2014 installation, and everything seems to be working again.

But, it is not correct!

When I check the new created object on the test server with this command

select [name] , [type], [type_desc]
from sys.objects
where name like 'spt_v%'

It shows a user_table object. On my other server, it shows a view...

So, my question is: How can I create the view spt_values which gets its data from a table spt_values?

Answer 1

Ok, after some fiddling arround, I found the solution..

The table sys.spt_values is in the ressources database (mssqlsystemresource). This database is only accessible when the SQL Service is started in single user mode..

To re-create the view I had to do the following steps:

1. Stop all SQL Services
   

2. Start the SQL Service in single user mode
Open a DOS Command prompt and start the sqlservice with the switch -m

sqlservr.exe -sSQLT01 –m

3. Connect SSMS to the instance
   

Just connect the query window, but not the Object Explorer window. The service only accepts one single connection! If there is a problem, you can see it in the DOS Window where the service is running.

4. Delete the wrong table spt_values
   

As I created a table spt_values on the master database, I have to delete it first

use master
go
drop table dbo.spt_values

5. Create the view
Now I finally can create the view dbo.spt_values, which points to the table sys.spt_values

use master
go
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

EXEC sp_MS_marksystemobject 'spt_values'
go

grant select on spt_values to public
go

6. Check the dbo.spt_values object

use master
select schema_name(schema_id), object_id('spt_values'), * 
from sys.objects
where name like 'spt_v%'

It should show a view now

7. Query the view dbo.spt_values and the table sys.spt_values
   

Just for the fun of it... You can now query the table sys.spt_values, which is in the ressources database

use mssqlsystemresource
Select * from sys.spt_values

And you can query the view dbo.spt_values, which is in the master database

use master
Select * from dbo.spt_values

8. Restart the services

You can now quit the DOS window with the SQL Service running and start the SQL Services. Or you just restart the whole server

Hope this post will help others in the future