August 21, 2014 at 5:06 am
Hello, if I want to get a list of column names which is better to use
INFORMATION_SCHEMA.COLUMNS or sys.columns
or are they the same?
Since with sys.columns the table name is found through the object_id would it be better just to use INFORMATION_SCHEMA.COLUMNS as you do not need to convert to get the table name?
Thank you
August 21, 2014 at 7:11 am
Thank you. I will be reading it soon.
August 21, 2014 at 7:22 am
Just an FYI, I prefer the system views to the information_schema views as there is more information contained in them. Also, information_schema views aren't that portable. They don't even exist in Oracle.
August 21, 2014 at 7:29 am
Lynn Pettis (8/21/2014)
Just an FYI, I prefer the system views to the information_schema views as there is more information contained in them. Also, information_schema views aren't that portable. They don't even exist in Oracle.
Thank you for the reply. Since we are a small shop, this code will not be ported, we only use SQL Server and we only need the one bit of information.
This post originated from my using the system views and the reviewer suggesting information_schema as he is old school.
I am just trying to learn about what I am using.:-)
August 21, 2014 at 8:00 am
djj (8/21/2014)
Lynn Pettis (8/21/2014)
Just an FYI, I prefer the system views to the information_schema views as there is more information contained in them. Also, information_schema views aren't that portable. They don't even exist in Oracle.Thank you for the reply. Since we are a small shop, this code will not be ported, we only use SQL Server and we only need the one bit of information.
This post originated from my using the system views and the reviewer suggesting information_schema as he is old school.
I am just trying to learn about what I am using.:-)
Then I would continue learning and using the system views. As your information needs expand you will be glad you did.
August 21, 2014 at 8:13 am
Quick note, INFORMATION_SCHEMA is an ANSI compliant metadata view collection added in 2000 if I remember correctly, sys.* is SQL Server specific.
😎
August 21, 2014 at 8:27 am
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. 😀
August 21, 2014 at 9:35 am
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.
😎
August 21, 2014 at 9:44 am
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.
August 21, 2014 at 10:01 am
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')
August 21, 2014 at 3:55 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) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply