SQL Server查看视图定义总结

 

在SQL Server中如何查看数据库视图的定义呢? 其实官方文档已经有一个较详细的总结了,这里在官方文档的基础上,我们再深入展开分析一下,例如如何获取系统视图的定义。知其然知其所以然吗。

 

1:使用SQL Server Management Studio(SSMS)

 

对象资源管理器中,首先找到对应数据库中需要查看定义的视图,右键单击获取对象的定义脚本。这种方式非常简单。在此略过。

 

 

2:通过脚本查看视图的定义。

 

 

可以通过下面三种方式获取定义脚本,如下所示

 

USE YourSQLDba;  
GO  
SELECT definition, uses_ansi_nulls, uses_quoted_identifier, is_schema_bound  
FROM sys.sql_modules  
WHERE object_id = OBJECT_ID('PerfMon.SessionInfo');   
GO  
 
 
 
 
 
USE YourSQLDba;  
GO 
SELECT OBJECT_DEFINITION (OBJECT_ID('PerfMon.SessionInfo')) AS ObjectDefinition;   
GO  
 
 
 
 
USE YourSQLDba;  
GO 
EXEC sp_helptext 'PerfMon.SessionInfo'; 

 

 

 

那么上面方式可以查看系统视图的定义吗? 实验验证测试一下即可知道。

 

1:首先,在SSMS的对象资源管理器中是是无法查看系统视图的定义的。SSMS直接屏蔽了相关功能。

 

2:上面三种脚本方式,sys.sql_modules 无法查看系统视图定义,内置函数OBJECT_DEFINITION、系统存储过程OBJECT_DEFINITION可以查看系统视图的定义。

 

   

 

 

系统视图无法查看对应的系统定义是因为条件限制原因(has_access(‘CO’, o.id) = 1),如下所示:

 

SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO
CREATE VIEW sys.sql_modules AS
    SELECT object_id = o.id,
        definition = object_definition(o.id),
        uses_ansi_nulls = sysconv(bit, o.status & 0x40000),             -- OBJMOD_ANSINULLS
        uses_quoted_identifier = sysconv(bit, o.status & 0x80000),      -- OBJMOD_QUOTEDIDENT
        is_schema_bound = sysconv(bit, o.status & 0x20000),             -- OBJMOD_SCHEMABOUND
        uses_database_collation = sysconv(bit, o.status & 0x100000),    -- OBJMOD_USESDBCOLL
        is_recompiled = sysconv(bit, o.status & 0x400000),              -- OBJMOD_NOCACHE
        null_on_null_input = sysconv(bit, o.status & 0x200000),         -- OBJMOD_NULLONNULL
        execute_as_principal_id = x.indepid,
        uses_native_compilation = sysconv(bit, case when (o.type = 'P') then o.status & 0x00000200 else 0 end)    -- OBJPRC_HEKATON
    FROM sys.sysschobjs o
    LEFT JOIN sys.syssingleobjrefs x ON x.depid = o.id AND x.class = 22 AND x.depsubid = 0 -- SRC_OBJEXECASOWNER
    WHERE o.pclass <> 100 -- x_eunc_Server
        AND ((o.type = 'TR' AND has_access('TR', o.id, o.pid, o.nsclass) = 1)
            OR (type IN ('P','V','FN','IF','TF','RF','IS') AND has_access('CO', o.id) = 1)
            OR (type IN ('R','D') AND o.pid = 0))
        AND (o.status2 & 0x00000020) = 0
 
GO

 

   

   

 

 

   

   

 

 

如果你到这一步以为就结束了的话,那么你太天真了。内置函数OBJECT_DEFINITION、系统存储过程OBJECT_DEFINITION对于有些视图也束手无策。如下所示,获取系统视图sys.parameters的定义如下。

CREATE VIEW sys.parameters
AS
    SELECT  object_id ,
            name ,
            parameter_id ,
            system_type_id ,
            user_type_id ,
            max_length ,
            PRECISION ,
            scale ,
            is_output ,
            is_cursor_ref ,
            has_default_value ,
            is_xml_document ,
            default_value ,
            xml_collection_id ,
            is_readonly ,
            is_nullable
    FROM    sys.parameters$
    WHERE   number = 1

 

 

其实对象sys.parameters$也是一个视图,正常情况下是无法查看sys.parameters$这个对象的,在专用管理员模式(DAC)下面才可以查看sys.parameters$的定义,而且只能通过系统内置函数OBJECT_DEFINITION,而sp_helptext 是会报错的。如果你要弄清楚一些系统视图的定义,那么基本上就要借助专用管理员模式(DAC)模式来查看。

 

 

USE YourSQLDba; 

GO

SELECT OBJECT_DEFINITION (OBJECT_ID(‘sys.parameters$’)) AS ObjectDefinition;  

GO 

 

CREATE VIEW sys.parameters$ AS
    SELECT c.id AS object_id,
        c.number, c.name,
        c.colid AS parameter_id,
        c.xtype AS system_type_id,
        c.utype AS user_type_id,
        c.length AS max_length,
        c.prec AS precision,
        c.scale AS scale,
        sysconv(bit, c.status & 512) AS is_output,        -- CPM_OUTPUT
        sysconv(bit, c.status & 1024) AS is_cursor_ref,    -- CPM_CURSORREF
        sysconv(bit, isnull(v.objid, 0)) AS has_default_value,
        sysconv(bit, c.status & 2048) AS is_xml_document, -- CPM_XML_DOC        
        v.value AS default_value,
        xmlns AS xml_collection_id,
        sysconv(bit, c.status & 4194304) AS is_readonly, -- CPM_IS_READONLY = 0x00400000
        sysconv(bit, 1 - (c.status & 1)) AS is_nullable -- CPM_NOTNULL  = 0x00000001
    FROM sys.syscolpars c
    LEFT JOIN sys.sysobjvalues v ON v.valclass = 9 AND v.objid = c.id AND v.subobjid = c.colid AND v.valnum = 0    -- SVC_PARAMDEFAULT
    WHERE number > 0 AND has_access('CO', c.id) = 1

 

其实在专用管理员模式(DAC)下面,虽然能查到sys.parameters$的定义,但是在基表sys.sysschobjs里面找不到sys.parameters$这个对象,其实你可以找到系统的Resource数据库

 

 

一般位于<drive>:\Program Files\Microsoft SQL Server\MSSQL<version>.<instance_name>\MSSQL\Binn\下面(以实际安装路径为准),名字为mssqlsystemresource.mdf ,你可以将其Copy到其它位置后,然后附加重命名为一个数据库,例如我附加为resource数据库,然后你就可以在SSMS界面直接获取系统视图的定义了。

 

 

 

 

 

参考资料:

 

https://docs.microsoft.com/en-us/sql/relational-databases/views/get-information-about-a-view

(0)
上一篇 2022年3月21日
下一篇 2022年3月21日

相关推荐