Lynn Pettis (8/21/2014)
Eirikur Eiriksson (8/21/2014)
Lynn Pettis (8/21/2014)
Eirikur Eiriksson (8/21/2014)
Quick note, INFORMATION_SCHEMA is an ANSI compliant metadata view collection added in 2000 if I remember correctly, sys.* is SQL Server specific.😎
It may be but it isn't necessarily implemented by all RDBS vendors. Plus, the there really isn't truly portable SQL code either. 😀
And even if it was implemented by most, any cross platform attempt would most likely fail anyway when it comes to using the metadata.
😎
Which is why I will stay with the system views, more information available if needed. If I switch RDBMS I'll just have to learn new things anyway.
Same here, especially when the INFORMATION_SCHEMA objects are mostly views querying the sys.* objects. The following definitions clearly show the difference
😎
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'
CREATE VIEW INFORMATION_SCHEMA.TABLES
AS
SELECT
DB_NAME()AS TABLE_CATALOG,
s.nameAS TABLE_SCHEMA,
o.nameAS TABLE_NAME,
CASE o.type
WHEN 'U' THEN 'BASE TABLE'
WHEN 'V' THEN 'VIEW'
ENDAS TABLE_TYPE
FROM
sys.objects o LEFT JOIN sys.schemas s
ON s.schema_id = o.schema_id
WHERE
o.type IN ('U', 'V')