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:
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.
INFORMATION_SCHEMA.TABLES view allows you to get information about all tables and views within a database. By default it will show you this information for every single table and view that is in the database.
Sys tables contain the all-important meta data, the data about your data. This data includes information about table names, column names, and data types, so that SQL Server can properly process queries and return result sets. System tables contain information about valid users and their permissions, so data can be secure, and information about your SQL Server configuration, so you can predict and control the system's behavior.
2条答案
按热度按时间enxuqcxy1#
Both
INFORMATION_SCHEMA
andsys
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 ofINFORMATION_SCHEMA
views, and each view exposes essentially identical information regardless of the vendor. In SQL Server, most, if not all, theINFORMATION_SCHEMA
views are views that go back to thesys
tables in one way or another. In SQL Server, you can see the underlying VIEW definitions by running queries like:Which returns:
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 thesys
tables and due to the ISO definitions for those views, it is not unheard of that theINFORMATION_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:
Returns:
This clearly returns much more detailed information, but notice that
INFORMATION_SCHEMA.TABLES
returns both user tables and views, whilesys.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 thesys
objects tables, and there are some situations where missing objects in theINFORMATION_SCHEMA
views can bite you. If I'm looking for a reliable and complete set of items, then I use thesys
tables (specificallysys.objects
orsys.all_objects
), but those require a lot more work to get readable results. TheINFORMATION_SCHEMA
views have done much of that work for you already.ozxc1zmp2#
INFORMATION_SCHEMA.TABLES view allows you to get information about all tables and views within a database. By default it will show you this information for every single table and view that is in the database.
Kindly visit this: https://www.mssqltips.com/sqlservertutorial/196/informationschematables/
Sys tables contain the all-important meta data, the data about your data. This data includes information about table names, column names, and data types, so that SQL Server can properly process queries and return result sets. System tables contain information about valid users and their permissions, so data can be secure, and information about your SQL Server configuration, so you can predict and control the system's behavior.