如何查看/编写系统视图的脚本定义? [英] how to see/script definitions of system views?

问题描述

我尝试在模型数据库和用户定义的数据库中执行来自 [1] 的脚本，但它们仅提供用户定义(非系统)视图的定义/脚本，即我无论如何都可以轻松从 GUI 获得的定义/脚本.

I tried to execute scripts from [1] in model database and user-defined databases but they give definitions/scripts of only user-defined (non-system) views, i.e. those that I anyway can easily get from GUI.

如何在 SQL Server 2008 R2 中查看/编写系统视图的定义/脚本?

How can I see/script the definition/script of a system view in SQL Server 2008 R2?

[1]
问题SQL Server 2005 中的系统视图文本"的答案
SQL Server 2005 中的系统视图文本

推荐答案

select object_definition(object_id('[sys].[server_permissions]')) 
      AS [processing-instruction(x)] FOR XML PATH('') 

(XML 位只是为了防止在 SSMS 中查看时长定义被截断)

(The XML bit is just to prevent long definitions getting truncated when viewed in SSMS)

您也可以停止 SQL Server 实例.复制 mssqlsystemresource 文件(在我的系统上，它位于 C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\Binn\mssqlsystemresource.mdf，然后以新名称重新附加副本).

You can also stop the SQL Server instance. Copy the mssqlsystemresource file (on my system this is at C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\Binn\mssqlsystemresource.mdf and then re-attatch the copy under a new name).

在重新附加的版本中，使用普通 SSMS 功能可以更轻松地浏览和查看各种定义.

In the reattached version it is easier to poke around and see the various definitions using normal SSMS functionality.

这篇关于如何查看/编写系统视图的脚本定义?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！