Determining index key size

• Adam Bean

  One Orange Chip

  Points: 26631

  More actions

  August 27, 2008 at 7:14 am

  #130994

  Hey all, does anyone have anything readily available that will display an index key size?

  Thanks

• Jack Corbett

  SSC Guru

  Points: 184394

  More actions

  August 27, 2008 at 8:59 am

  #863790

  I don't have anything done, but it shouldn't be too hard using the sys views. The interesting part would be adding the clustered key to non-clustered indexes and then figuring out indexes that need to have the uniqueifer added and adding that in as well. This would be a start:

  [font="Courier New"]SELECT

     O.name AS table_name,

     O.type_desc AS table_type,

     I.name AS index_name,

     I.type_desc AS index_type,

     SUM(C.max_length) AS max_index_key_size

  FROM

     sys.indexes I JOIN

     sys.index_columns IC ON

         I.OBJECT_ID = IC.OBJECT_ID AND

         I.index_Id = IC.index_id JOIN

     sys.columns C ON

         I.OBJECT_ID = C.OBJECT_ID AND

         IC.column_id = C.column_id JOIN

     sys.all_objects O ON

         I.OBJECT_ID = O.OBJECT_ID

  WHERE

     O.type_desc = 'USER_TABLE'

  GROUP BY

     O.name,

     O.type_desc,

     I.name,

     I.type_desc

  ORDER BY

     table_name,

     index_name

  [/font]

  Jack Corbett
  Consultant - Straight Path Solutions
  Check out these links on how to get faster and more accurate answers:
  Forum Etiquette: How to post data/code on a forum to get the best help
  Need an Answer? Actually, No ... You Need a Question