视图存储在SQLServer中的哪个位置-WhereareviewsstoredinSQLServer

5  

The pedantic answer to your question is... only Microsoft knows exactly where view metadata is physically stored. In the move from SQL 2000 to SQL 2005 (on which 2008 is based) MS got rid of direct access to system tables where views used to be literally stored (dbo.sysviews and dbo.syscomments) and added a layer of abstraction (via the hidden resources database) which means you can only access meta data about views via catalog views. INFORMATION_SCHEMA is an ANSI compliant set of catalog views. While marginally useful for their relative portability between versions, often more information is available from the sql 2008 catalog views - in this case sys.views and sys.sql_modules

对您的问题的迂腐回答是......只有Microsoft知道视图元数据的物理存储位置。在从SQL 2000迁移到SQL 2005（基于2008年）的过程中，MS摆脱了对系统表的直接访问，在这些系统表中，以前存储了视图（dbo.sysviews和dbo.syscomments）并添加了一个抽象层（通过隐藏资源数据库）这意味着您只能通过目录视图访问有关视图的元数据。 INFORMATION_SCHEMA是一组符合ANSI标准的目录视图。虽然版本之间的相对可移植性稍微有用，但通常可以从sql 2008目录视图获得更多信息 - 在本例中为sys.views和sys.sql_modules

Be aware that views can be created with the ENCRYPTION option set which encrypts the sys.comments record(s) that contain the SQL definition of the view. But if not encrypted, then sp_helptext [MyView] will give you a quick look at the definition.

请注意，可以使用ENCRYPTION选项集创建视图，该选项用于加密包含视图的SQL定义的sys.comments记录。但如果没有加密，那么sp_helptext [MyView]会让你快速查看定义。

edited as per 1st comment below, to replace "sys.comments" with "sys.sql_modules"

根据下面第1条评论编辑，用“sys.sql_modules”替换“sys.comments”

3  

Note: Based on this post http://improve.dk/archive/2012/08/27/where-does-sql-server-store-the-source-for-stored-procedures.aspx, is very likely that the definition of views are stored (also) in sys.sysobjvalues system table.

注意：根据这篇文章http://improve.dk/archive/2012/08/27/where-does-sql-server-store-the-source-for-stored-procedures.aspx，很可能是定义视图（也）存储在sys.sysobjvalues系统表中。

The list of all user T-SQL modules (within SQL Server 2008) can be queried using sys.sql_modules system view (link). Here, you can find the definitions of user views (column definition):

可以使用sys.sql_modules系统视图（链接）查询所有用户T-SQL模块的列表（在SQL Server 2008中）。在这里，您可以找到用户视图的定义（列定义）：

SELECT  QUOTENAME(s.name)+'.'+QUOTENAME(o.name) AS full_object_name, 
        m.*
FROM    sys.sql_modules m
JOIN    sys.objects o ON m.object_id=o.object_id
JOIN    sys.schemas s ON o.schema_id=s.schema_id
WHERE   o.type='V' -- only view objects
ORDER BY full_object_name

If you run EXEC sp_helptext 'sys.sql_modules' you will get the source code of this system view:

如果运行EXEC sp_helptext'sys.sql_modules'，您将获得此系统视图的源代码：

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
    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
        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))

You can see that this view queries another system object sys.sysschobjs that, I think, is the system table used to store definition of views .

您可以看到此视图查询另一个系统对象sys.sysschobjs，我认为它是用于存储视图定义的系统表。

Note 1: Using INFORMATION_SCHEMA.VIEWS to find definition of a view is not a reliable method because INFORMATION_SCHEMA.VIEWS.VIEW_DEFINITION column definition is convert(nvarchar(4000), object_definition(object_id)) (max. 4000 chars).

注1：使用INFORMATION_SCHEMA.VIEWS查找视图的定义不是一种可靠的方法，因为INFORMATION_SCHEMA.VIEWS.VIE​​W_DEFINITION列定义是转换的（nvarchar（4000），object_definition（object_id））（最多4000个字符）。

Note 2: Instead, you should use sys.sql_modules.definition column: definition = object_definition(o.id). If you look at object_definition function (link) you will see that return type is nvarchar(max).

注意2：相反，您应该使用sys.sql_modules.definition列：definition = object_definition（o.id）。如果查看object_definition函数（链接），您将看到返回类型为nvarchar（max）。

1  

In a system table.

在系统表中。

The following query will retrieve them...

以下查询将检索它们......

SELECT TABLE_NAME as ViewName, VIEW_DEFINITION as ViewDefinition FROM INFORMATION_SCHEMA.Views

SELECT TABLE_NAME为ViewName，VIEW_DEFINITION为ViewDefinition FROM INFORMATION_SCHEMA.Views

To view edit them normally you would look in the view folder under tables in studio manager.

要查看正常编辑它们，您将查看工作室管理器中表格下的视图文件夹。

You can create/edit them from this folder using the designer or write scripts.

您可以使用设计器或编写脚本从此文件夹创建/编辑它们。

0  

If you mean the tables the view produces then the answer is that they aren't stored at all. A view is just a query, and that is all it stores. When you query a view the db engine just fetches your view query's results and then queries those.

如果你的意思是视图生成的表，那么答案是它们根本不存储。视图只是一个查询，就是它存储的全部内容。查询视图时，db引擎只会获取视图查询的结果，然后查询这些结果。

DB engines can store 'materialized' views, but that's a different topic.

数据库引擎可以存储“物化”视图，但这是一个不同的主题。

0  

View is a simple SQL statement that is stored in database schema (INFORMATION_SCHEMA.Views). So when ever we call the view the SQL statement gets executed and return the rows from main physical table.

View是一个存储在数据库模式（INFORMATION_SCHEMA.Views）中的简单SQL语句。因此，当我们调用视图时，SQL语句将被执行并从主物理表返回行。

You can also tell the view as a Logical table that store the defination (the sql statement) but not the result.

您还可以将视图告知为存储defination（sql语句）但不存储结果的逻辑表。

You can see the defination using below statement, as said by Dan above, Only if the view defination is Not encrypted: SELECT TABLE_NAME as ViewName, VIEW_DEFINITION as ViewDefinition FROM INFORMATION_SCHEMA.Views

您可以使用以下语句查看定义，如上面Dan所述，仅当视图定义未加密时：SELECT TABLE_NAME为ViewName，VIEW_DEFINITION为ViewDefinition FROM INFORMATION_SCHEMA.Views

More details on View @ MSDN.

有关View @ MSDN的更多详细信息。