SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
More on indices (SQL Server 2k8)

 
Reply to topic    SoftTree Technologies Forum Index » Tips & Snippets Repository View previous topic
View next topic
More on indices (SQL Server 2k8)
Author Message
gemisigo



Joined: 11 Mar 2010
Posts: 2102

Post More on indices (SQL Server 2k8) Reply with quote
The following snippets are an easy way to have a peek at the indices of a table (or their details). Both work with Action set to Display Output Results.

Listing indices of the selected table:
Code:

-- index list for a table
$PROMPT(show_disabled,List disabled indices?,,,Yes No)$
$$
SELECT
   s.[schema_id]
   ,s.[name] AS [schema_name]
   ,t.[object_id] AS table_id
   ,t.[name] AS table_name
   ,i.index_id
   ,i.[name] AS index_name
   ,i.type_desc
   ,i.is_primary_key, i.is_unique, i.is_disabled, i.has_filter
   ,COUNT(ic.index_column_id) - SUM(CAST(ic.is_included_column as SMALLINT)) AS ic_count
   ,SUM(CAST(ic.is_included_column as SMALLINT)) AS iic_count
FROM
   sys.schemas s
   INNER JOIN
   sys.tables t
      ON   t.schema_id = s.schema_id
   INNER JOIN
   sys.indexes i
      ON   i.object_id = t.object_id
   INNER JOIN
   sys.index_columns ic
      ON   ic.object_id = t.object_id
      AND   ic.index_id = i.index_id
   INNER JOIN
   sys.columns c
      ON   c.object_id = t.object_id
      AND   ic.column_id = c.column_id
WHERE   i.index_id > 0
   AND   i.type IN ( 1 ,2 ) -- clustered & nonclustered only
   AND   i.is_hypothetical = 0
   AND ( i.is_disabled = 0 OR '$show_disabled$' = 'Yes')
   AND t.[object_id] = OBJECT_ID('$OBJECT$')
GROUP BY
   s.[schema_id], s.[name], t.[object_id], t.[name], i.index_id, i.[name],i.is_primary_key, i.is_unique, i.is_disabled, i.has_filter,i.type_desc
ORDER BY
   i.[name]
$$


Show the details of an index of the selected table (identified by index_id you can get by using the snippet above):
Code:

-- detailed index view for a table
$PROMPT(idix,Index id?)$
$$
SELECT
   s.[schema_id]
   ,s.[name] AS [schema_name]
   ,t.[object_id] AS table_id
   ,t.[name] AS table_name
   ,i.index_id
   ,i.[name] AS index_name
   ,i.is_primary_key, i.is_unique, i.is_disabled, i.has_filter
   ,ic.index_column_id, c.[name] AS column_name
   ,CASE WHEN ic.is_descending_key = 0 THEN 'ASC'
        ELSE 'DESC'
    END AS [order]
    ,ic.is_included_column
FROM
   sys.schemas s
   INNER JOIN
   sys.tables t
      ON   t.schema_id = s.schema_id
   INNER JOIN
   sys.indexes i
      ON   i.object_id = t.object_id
   INNER JOIN
   sys.index_columns ic
      ON   ic.object_id = t.object_id
      AND   ic.index_id = i.index_id
   INNER JOIN
   sys.columns c
      ON   c.object_id = t.object_id
      AND   ic.column_id = c.column_id
WHERE   i.index_id > 0
   AND   i.type IN ( 1 ,2 ) -- clustered & nonclustered only
   AND   i.is_hypothetical = 0
   AND t.[object_id] = OBJECT_ID('$OBJECT$')
   AND i.index_id = $idix$
ORDER BY
   i.[name]
   ,ic.index_column_id
$$

Wed Aug 24, 2011 5:30 am View user's profile Send private message
Display posts from previous:    
Reply to topic    SoftTree Technologies Forum Index » Tips & Snippets Repository All times are GMT - 4 Hours
Page 1 of 1

 
Jump to: 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


 

 

Powered by phpBB © 2001, 2005 phpBB Group
Design by Freestyle XL / Flowers Online.