Difference between Information_schema vs sys tables in SQL Server

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

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 another. In SQL Server, you can see the underlying VIEW definitions by running queries like:

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

Which returns:

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

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

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 unheard of that 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.)

That said, however:

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

Returns:

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"

This clearly returns much more detailed information, but notice that INFORMATION_SCHEMA.TABLES returns both user tables and views, while sys.tables only returns user tables.

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. Still, 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 much of that work for you already.