Difference between Information_schema vs sys tables in SQL Server

Question

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

Answer 1

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 other. 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 (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.

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

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' 

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.

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.