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

Question

在我的测试 SQL Server 2014 安装中，我正在“清理”master 数据库。

使用以下命令，我检查了哪些用户对象:

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%'

其中一个结果是 View spt_values。

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

因为它不是我所知道的 View 之一，所以我删除了它(连同其他对象)。

那天晚些时候，我想在 SSMS 2016 中检查数据库的属性，结果出现以下错误:

经过一番搜索，我发现我可以使用脚本 u_tables.sql(位于您服务器上的 SQL Server 安装文件夹中)重新创建丢失的 View 。来自这里的信息:https://ashishgilhotra.wordpress.com/tag/u_tables-sql/

该脚本中用于创建 View 的代码如下:

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

在查看代码时，我已经怀疑它能否工作，因为在任何地方都找不到 sys.spt_values 表。

正如预期的那样，我得到了错误

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

在我的另一台装有 SQL Server 2008 的服务器上，有一个表 master.dbo.spt_values(但没有 View )!

经过更多的搜索，我发现我可以创建一个同名的表。链接在这里https://www.mssqltips.com/sqlservertip/3694/fix-invalid-object-name-masterdbosptvalues-when-viewing-sql-server-database-properties/

现在我用另一个 SQL Server 2014 安装的值创建了一个表，一切似乎又恢复正常了。

但是，它不 正确!

当我用这个命令在测试服务器上检查新创建的对象时

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

它显示了一个 user_table 对象。在我的另一台服务器上，它显示了一个 View ...

所以，我的问题是:如何创建从表 spt_values 获取数据的 View spt_values？

Answer 1

好吧，经过一番折腾，我找到了解决方案..

表 sys.spt_values 位于资源数据库 (mssqlsystemresource) 中。只有在单用户模式下启动 SQL 服务时才能访问此数据库。

要重新创建 View ，我必须执行以下步骤:

1. 停止所有 SQL 服务
   

2.以单用户模式启动SQL服务
打开 DOS 命令提示符并使用开关 -m 启动 sqlservice

sqlservr.exe -sSQLT01 –m

1. 将 SSMS 连接到实例
   

只连接查询窗口，不连接对象资源管理器窗口。该服务只接受一个连接!如果有问题，您可以在服务运行的DOS窗口中看到。

1. 删除错误的表spt_values
   

因为我在 master 数据库上创建了一个表 spt_values，所以我必须先删除它

use master
go
drop table dbo.spt_values

5. 创建 View
现在我终于可以创建 View dbo.spt_values，它指向表 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. 检查 dbo.spt_values 对象

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

现在应该显示一个 View

1. 查询 View dbo.spt_values 和表 sys.spt_values
   

只是为了好玩...您现在可以查询资源数据库中的表 sys.spt_values

use mssqlsystemresource
Select * from sys.spt_values

并且可以查询master数据库中的 View dbo.spt_values

use master
Select * from dbo.spt_values

8. 重启服务

您现在可以在 SQL 服务运行的情况下退出 DOS 窗口并启动 SQL 服务。或者你只是重启整个服务器

希望这篇文章对以后的其他人有帮助