Irfan's World

February 17, 2011

How to get all indexes of table(s) in SQL Server 2008?

Filed under: SQL Scripts — Tags: , , , , — Irfan @ 11:20 pm

1) To get the indexes from a given table and schema.

Use the following script to get all the indexes for a given table and schema name.

DECLARE @TableName SYSNAME,
      
@SchemaName
SYSNAME

SET @TableName = 'Employee'
SET @SchemaName =
'HumanResources'
SELECT
  
i.OBJECT_ID
,
  
i.index_id
,
  
i.name IndexName
,
  
OBJECT_NAME(i.OBJECT_ID) TableName
,
  
c.name ColumnName
,
  
i.index_id
,
  
df.name FileGroupName
,
  
i.fill_factor
,
  
i.is_unique
,
  
i.type_desc
,
  
ic.is_included_column
,
  
schema_name(o.schema_id)
SchemaName
FROM
sys.indexes i
INNER JOIN
sys.index_columns ic
  
ON      i.index_id = ic.index_id
AND
          
i.OBJECT_ID = ic.
OBJECT_ID
INNER JOIN
sys.columns c
  
ON      ic.column_id = c.column_id
AND
          
i.OBJECT_ID = c.
OBJECT_ID
INNER JOIN
sys.database_files df
  
ON i.data_space_id =
df.data_space_id
INNER JOIN
sys.objects o
  
ON i.OBJECT_ID = o.
OBJECT_ID
WHERE
  
OBJECT_NAME(i.OBJECT_ID) = @TableName
AND
  
schema_name(o.schema_id) = @SchemaName
AND
  
i.index_id <>
1
ORDER BY
i.index_id

Following is the result when I run the above query for HumanResources Schema.

image

   2) To get all the indexes for a complete schema you can comment the line that checks for the TableName in the WHERE clause.

   DECLARE -- @TableName sysname,
      
@SchemaName
SYSNAME

   -- SET @TableName = 'customer'
   SET @SchemaName =
'sales'
   SELECT
     
i.OBJECT_ID
,
     
i.index_id
,
     
i.name IndexName
,
    
OBJECT_NAME(i.OBJECT_ID) TableName
,
    
c.name ColumnName
,
    
i.index_id
,
    
df.name FileGroupName
,
    
i.fill_factor
,
    
i.is_unique
,
    
i.type_desc
,
    
ic.is_included_column
,
    
schema_name(o.schema_id)
SchemaName
  FROM
sys.indexes i
  INNER JOIN
sys.index_columns ic
     
ON      i.index_id = ic.index_id
AND
             
i.OBJECT_ID = ic.
OBJECT_ID
  INNER JOIN
sys.columns c
    
ON      ic.column_id = c.column_id
AND
          
i.OBJECT_ID = c.
OBJECT_ID
  INNER JOIN
sys.database_files df
    
ON i.data_space_id =
df.data_space_id
  INNER JOIN
sys.objects o
    
ON i.OBJECT_ID = o.
OBJECT_ID
  WHERE
    -- object_name(i.object_id) = @TableName AND
   
schema_name(o.schema_id) = @SchemaName
AND
   
i.index_id <>
1
  ORDER BY
i.index_id

image

The above script is useful to find table column indexes and I use it when I develop filters or objects that can go to WHERE part of the query panel while developing reports using Web Intelligence (Rich Client) or Desktop Intelligence and it boost the performance of the query and that is all what we need at the end of the day.

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: