SQL Server 中 Information_schema 与 sys 表的区别 [英] Difference between Information_schema vs sys tables in SQL Server

问题描述

SQL Server 中的 information_schema 表和 sys 表是什么?它们之间有什么区别?

What are the information_schema tables and sys tables in SQL Server? What are the differences between them?

推荐答案

INFORMATION_SCHEMA 和 sys 对象都是 SQL Server 中可用的元数据目录.

Both INFORMATION_SCHEMA and sys objects are both metadata catalogs that are available in SQL Server.

INFORMATION_SCHEMA 视图集 是元数据的 ANSI/ISO 标准目录.大多数 RDBMS 支持大多数 INFORMATION_SCHEMA 视图，并且无论供应商如何，每个视图都公开基本相同的信息.在 SQL Server 中，大多数(如果不是全部)INFORMATION_SCHEMA 视图都是以某种方式返回到 sys 表的视图.在 SQL Server 中，您可以通过运行以下查询来查看底层 VIEW 定义:

The INFORMATION_SCHEMA set of views are the ANSI/ISO standard catalogs for metadata. Most RDBMSs support the majority of INFORMATION_SCHEMA views, and each view exposes essentially identical information regardless of the vendor. In SQL Server, most, if not all the INFORMATION_SCHEMA views are views that go back to the sys tables in one way or other. In SQL Server, you can see the underlying VIEW definitions by running queries like:

SELECT OBJECT_DEFINITION(OBJECT_ID('INFORMATION_SCHEMA.TABLES'))    

哪个返回:

CREATE VIEW INFORMATION_SCHEMA.TABLES
AS 
SELECT
    DB_NAME()           AS TABLE_CATALOG,
    s.name              AS TABLE_SCHEMA,
    o.name              AS TABLE_NAME,
    CASE o.type
        WHEN 'U' THEN 'BASE TABLE'
        WHEN 'V' THEN 'VIEW'
    END             AS TABLE_TYPE
FROM
    sys.objects o LEFT JOIN sys.schemas s
    ON s.schema_id = o.schema_id
WHERE
    o.type IN ('U', 'V')

sys 表和视图 是原始元数据目录视图和表，AFAIK 由 Sybase(Microsoft 从中购买 SQL Server 的原始代码库的供应商)创建.大多数 RDBMS 都有一组等效的目录表，但具体的表名称因供应商而异.在 SQL Server 中，这些表以及后来添加的动态管理视图 (DMV) 是 Microsoft 创建的，用于捕获数据库的元数据以供系统和用户使用.

The sys tables and views are the original metadata catalog views and tables that were, AFAIK, created by Sybase (the vendor that Microsoft purchased SQL Server's original code base from). Most RDBMSs have an equivalent set of catalog tables, but the specific table names are all different between vendors. In SQL Server, these tables along with the later addition of the dynamic management views (DMVs) are what Microsoft created to capture a database's metadata for system and user use.

在 SQL Server 中，由于 INFORMATION_SCHEMA 视图通常指向 sys 表，并且由于这些视图的 ISO 定义，INFORMATION_SCHEMA 视图不包含所有元数据或所有您正在寻找的对象<​​/a>.(我个人认为 Aaron 在那篇文章中的偏见有点夸大其词，但他可能比我更受这个问题的困扰，而且他也可能处理比我更复杂配置的数据库.)

In SQL Server, since the the INFORMATION_SCHEMA views typically point back to the sys tables and due to the ISO definitions for those views, it is not uncommon for the INFORMATION_SCHEMA views do not contain all metadata or all objects that you're looking for. (Personally I think Aaron's bias in that article is a little overblown, but he's probably been bitten by the issue more than I have and he also probably works on more complexly configured databases than I do.)

话虽如此:

SELECT OBJECT_DEFINITION(OBJECT_ID('sys.tables'))

返回:

CREATE VIEW sys.tables AS
    SELECT o.name, o.object_id, o.principal_id, o.schema_id, o.parent_object_id,
        o.type, o.type_desc, o.create_date, o.modify_date,
        o.is_ms_shipped, o.is_published, o.is_schema_published,
        isnull(ds.indepid, 0) AS lob_data_space_id,
        rfs.indepid AS filestream_data_space_id,
        o.property AS max_column_id_used,
        o.lock_on_bulk_load, o.uses_ansi_nulls, o.is_replicated, o.has_replication_filter,
        o.is_merge_published, o.is_sync_tran_subscribed, o.has_unchecked_assembly_data,
        lob.intprop AS text_in_row_limit,
        o.large_value_types_out_of_row,
        o.is_tracked_by_cdc,
        o.lock_escalation_option AS lock_escalation,
        ts.name AS lock_escalation_desc,
        o.is_filetable,
        o.is_memory_optimized,
        o.durability_option as durability,
        d.name as durability_desc
    FROM sys.objects$ o
    LEFT JOIN sys.sysidxstats lob ON lob.id = o.object_id AND lob.indid <= 1
    LEFT JOIN sys.syssingleobjrefs ds ON ds.depid = o.object_id AND ds.class = 8 AND ds.depsubid <= 1   -- SRC_INDEXTOLOBDS 
    LEFT JOIN sys.syssingleobjrefs rfs ON rfs.depid = o.object_id AND rfs.class = 42 AND rfs.depsubid = 0   -- SRC_OBJTOFSDS
    LEFT JOIN sys.syspalvalues ts ON ts.class = 'LEOP' AND ts.value = o.lock_escalation_option
    LEFT JOIN sys.syspalvalues d ON d.class = 'DOPT' AND d.value = o.durability_option
    WHERE o.type = 'U'

这显然返回了更多详细信息，请注意 INFORMATION_SCHEMA.TABLES 返回用户表和视图，而 sys.tables 仅返回用户表.

Which is clearly returning a lot more detailed information, and notice that INFORMATION_SCHEMA.TABLES returns both user tables and views, while sys.tables only returns user tables.

就我个人而言，我发现 INFORMATION_SCHEMA 视图组织得更好，更容易用于即席查询以按名称查找表或按名称查找列，但在某些极端情况下，您必须去到 sys 对象表，并且在某些情况下，INFORMATION_SCHEMA 视图中缺少对象可能会让您感到厌烦.如果我正在寻找一组可靠且完整的项目，那么我会使用 sys 表(特别是 sys.objects 或 sys.all_objects)但是这些需要做更多的工作才能获得可读的结果.INFORMATION_SCHEMA 视图已经为您完成了很多工作.

Personally, I find the INFORMATION_SCHEMA views much better organized and much easier to use for ad hoc queries to find tables by name or columns by name, but there are some corner cases where you have to go to the sys objects tables and there are some situations where missing objects in the INFORMATION_SCHEMA views can bite you. If I'm looking for a reliable and complete set of items then I use the sys tables (specifically sys.objects or sys.all_objects) but those require a lot more work to get readable results. The INFORMATION_SCHEMA views have done a lot of that work for you already.

这篇关于SQL Server 中 Information_schema 与 sys 表的区别的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！