Column info

  • Hi All,

    Is there any way in which we can get the list of non-indexed columns in a table or a whole database?

    Thanks in advance,


  • Sure you can using sys.columns, sys.index_columns, etc.

    But I'm curious - do you have a purpose for doing this or just curiosity?

  • @pam

    Thanks a lot for your reply. One of our managers asked me to find this info.

    Is there any script for this.

    Thanks in advance,


  • Dangerous, getting this type of thing to a manager.

    Only semi-tested, but this should get you there:


    OBJECT_NAME(c.object_id) AS thetable


    FROM sys.index_columns AS ic

    RIGHT OUTER JOIN sys.columns AS c

    ON ic.object_id = c.object_id

    AND ic.column_id = c.column_id

    WHERE ic.object_id IS null

    Run this on the DB for which you want the info.

    Please check back with us if your manager gives you any action items/ directions around this. I just have a bad feeling I know where that manager is headed...

  • Tables columns that are not in an index:

    selectschemas.nameas schema_name

    ,tables.nameas table_name

    ,columns.nameas column_name



    ontables.schema_id= schemas.schema_id


    on columns.object_id = tables.object_id


    (select 1


    whereindex_columns.object_id= columns.object_id

    andindex_columns.column_id= columns.column_id


    SQL = Scarcely Qualifies as a Language

  • Pam and Carl,

    Wow! How beautiful and excellent are your replies! Thank you very much,


Viewing 6 posts - 1 through 5 (of 5 total)

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