thumatinagaraju

 Profile Answers by thumatinagaraju Questions by thumatinagaraju

• Oct 24th, 2006

 

u can get the type of index from the data dictonary ok suppose if u hav emp table use following sysntax to get the type of index ..SQL> SELECT ic.index_name, ic.column_name, 2 ic.column_position col_pos,ix.uniqueness 3 FROM user_indexes ix, user_ind_columns ic 4 WHERE ic.index_name = ix.index_name 5 AND ic.table_name = 'EMP';

Nigam

• Nov 12th, 2006

 

I feel this query is for ORACLE .

to know What are the index one table has this the comand for SQL server

sp_helpindex <Table Name >

atdhdrolla

 Profile Answers by atdhdrolla Questions by atdhdrolla

• Jun 12th, 2008

 

sp_helpindex <TableName>

works for SQL server 2005

kirandba

 Profile Answers by kirandba 

• Mar 31st, 2010

 

Use below query to know index usage in SQL Server

SELECT   OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME],
         I.[NAME] AS [INDEX NAME],
         USER_SEEKS,
         USER_SCANS,
         USER_LOOKUPS,
         USER_UPDATES ,LAST_USER_SEEK,LAST_USER_SCAN,LAST_USER_LOOKUP
FROM     SYS.DM_DB_INDEX_USAGE_STATS AS S
         INNER JOIN SYS.INDEXES AS I
           ON I.[OBJECT_ID] = S.[OBJECT_ID]
              AND I.INDEX_ID = S.INDEX_ID
WHERE    OBJECTPROPERTY(S.[OBJECT_ID],'IsUserTable') = 1
Order by OBJECT_NAME(S.[OBJECT_ID])