Script to display index uniqueness

• Stevenson-76496

  Grasshopper

  Points: 23

  More actions

  March 25, 2005 at 4:36 pm

  #362251

  I am from the Oracle world and use the following script to display unique and nonunique indexes

  select index_name, table_name, uniqueness

  from user_indexes

  How do I display this information in SQL Server? 

  Thanks.

   

• Scott Thornburg

  Hall of Fame

  Points: 3347

  More actions

  March 27, 2005 at 3:35 pm

  #549069

  This would be much easier if there were INFORMATION_SCHEMA views for indexes.  Instead, you need to go some system table work:

  Try something like:

  select object_name(id) as TableName

       , name as IndexName

       , indexproperty(id, name, 'IsUnique') as IsUnique

  from  sysindexes

  where objectproperty(id, 'IsMSShipped') = 0

    and indexproperty(id, name, 'IsStatistics') = 0

    and indid not in (0,255)

  order by TableName

  The third column has value 1 if unique, 0 if not unique.  The where clause filters out system objects (leaving user objects), statistics -- which show up in sysindexes are are not truly indexes -- and non-clustered table and text entries.

  Hope this helps,

  Scott Thornburg

   

• Stevenson-76496

  Grasshopper

  Points: 23

  More actions

  March 28, 2005 at 5:24 am

  #549089

  Hi Scott,

  Thanks.  Your script returned the information I needed.

  Again thanks.