SQL Server檢視視圖定義總結

原文: 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界面直接擷取系統視圖的定義了。

注意，在SQL Server 2014測試發現，SQL Server裡面看不到System Tables下的相關基表了，應該是其屏蔽了這些功能，下面截圖是在SQL Server 2008 R2測試環境。

參考資料： https://docs.microsoft.com/en-us/sql/relational-databases/views/get-information-about-a-view