Difference between INFORMATION_SCHEMA.COLUMNS and sys.columns

  • 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

  • Thank you. I will be reading it soon.

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

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

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

  • Quick note, INFORMATION_SCHEMA is an ANSI compliant metadata view collection added in 2000 if I remember correctly, sys.* is SQL Server specific.

    😎

  • 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. 😀

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

    😎

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

  • 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')

  • 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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply