Group: General Forum Members
Last Login: Today @ 3:54 PM
|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.lock_escalation_option AS lock_escalation,
ts.name AS lock_escalation_desc,
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
DB_NAME() AS TABLE_CATALOG,
s.name AS TABLE_SCHEMA,
o.name AS TABLE_NAME,
WHEN 'U' THEN 'BASE TABLE'
WHEN 'V' THEN 'VIEW'
END AS TABLE_TYPE
sys.objects o LEFT JOIN sys.schemas s
ON s.schema_id = o.schema_id
o.type IN ('U', 'V')
Hall of Fame
Group: General Forum Members
Last Login: Today @ 1:45 PM
|This is an easy one:
ALWAYS use sys.; NEVER use INFORMATION_SCHEMA.
Not only might I_S be inaccurate, I've found it's also much slower and much more prone to locking/deadlocking. The I_S view definitions perhaps don't look like they should be, but they are.
SQL DBA,SQL Server MVP('07, '08, '09)
"If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them." James Blackburn, in closing argument in the "Fatal Vision" murders trial