Restore Table spt_values

README.md

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 to do the following steps:

1. Stop all MSSSQL Service

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

"C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Binn\sqlservr.exe" -m

3. Connect SSMS to the instance: Open 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