CODEWITHSUNDEEP
sqlsql-server-2008indexing
sine
sine

Reputation: 1049

Query to check index on a table

I need a query to see if a table already has any indexes on it.

Upvotes: 104

Views: 556142

Answers (11)

kj3
kj3

Reputation: 125

for smss - mssql, one can refer the create scriptenter image description here

Upvotes: -1

killi suman
killi suman

Reputation: 243

Simply you can find index name and column names of a particular table using below command

SP_HELPINDEX 'tablename'

It works for me

Upvotes: 20

SunilBk
SunilBk

Reputation: 31

check this as well This gives an overview of associated constraints across a database. Please also include facilitating where condition with table name of interest so gives information faster.

   select 
a.TABLE_CATALOG as DB_name,a.TABLE_SCHEMA as tbl_schema, a.TABLE_NAME as tbl_name,a. CONSTRAINT_NAME as constraint_name,b.CONSTRAINT_TYPE
 from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE a
join INFORMATION_SCHEMA.TABLE_CONSTRAINTS b on
 a.CONSTRAINT_NAME=b.CONSTRAINT_NAME

Upvotes: 2

Tejasvi Hegde
Tejasvi Hegde

Reputation: 2922

Created a stored procedure to list indexes for a table in database in SQL Server

create procedure _ListIndexes(@tableName nvarchar(200))
as
begin
/*
exec _ListIndexes '<YOUR TABLE NAME>'
*/
SELECT DB_NAME(DB_ID()) as DBName,SCH.name + '.' + TBL.name AS TableName,IDX.name as IndexName, IDX.type_desc AS IndexType,COL.Name as ColumnName,IC.*
    FROM sys.tables AS TBL 
         INNER JOIN sys.schemas AS SCH ON TBL.schema_id = SCH.schema_id 
         INNER JOIN sys.indexes AS IDX ON TBL.object_id = IDX.object_id 
         INNER JOIN sys.index_columns IC ON  IDX.object_id = IC.object_id and IDX.index_id = IC.index_id 
         INNER JOIN sys.columns COL ON ic.object_id = COL.object_id and IC.column_id = COL.column_id 
        where TBL.name = @tableName
    ORDER BY TableName,IDX.name

end

Upvotes: 5

Ed_
Ed_

Reputation: 1117

First you check your table id (aka object_id)

SELECT * FROM sys.objects WHERE type = 'U' ORDER BY name

then you can get the column's names. For example assuming you obtained from previous query the number 4 as object_id 

SELECT c.name
FROM sys.index_columns ic
INNER JOIN sys.columns c ON  c.column_id = ic.column_id
WHERE ic.object_id = 4 
AND c.object_id = 4

Upvotes: 3

FerranB
FerranB

Reputation: 36827

On Oracle:

  • Determine all indexes on table:

    SELECT index_name 
 FROM user_indexes
 WHERE table_name = :table

  • Determine columns indexes and columns on index:

    SELECT index_name
     , column_position
     , column_name
  FROM user_ind_columns
 WHERE table_name = :table
 ORDER BY index_name, column_order

References:

Upvotes: 3

Be Kind To New Users
Be Kind To New Users

Reputation: 10073

Here is what I used for TSQL which took care of the problem that my table name could contain the schema name and possibly the database name:

DECLARE @THETABLE varchar(100);
SET @THETABLE = 'theschema.thetable';
select i.*
  from sys.indexes i
 where i.object_id = OBJECT_ID(@THETABLE)
   and i.name is not NULL;

The use case for this is that I wanted the list of indexes for a named table so I could write a procedure that would dynamically compress all indexes on a table.

Upvotes: 5

Sam Salim
Sam Salim

Reputation: 2382

If you just need the indexed columns EXEC sp_helpindex 'TABLE_NAME'

Upvotes: 34

Donnie
Donnie

Reputation: 46933

Most modern RDBMSs support the INFORMATION_SCHEMA schema. If yours supports that, then you want either INFORMATION_SCHEMA.TABLE_CONSTRAINTS or INFORMATION_SCHEMA.KEY_COLUMN_USAGE, or maybe both.

To see if yours supports it is as simple as running

select count(*) from INFORMATION_SCHEMA.TABLE_CONSTRAINTS

EDIT: SQL Server does have INFORMATION_SCHEMA, and it's easier to use than their vendor-specific tables, so just go with it.

Upvotes: 8

gkrogers
gkrogers

Reputation: 8356

On SQL Server, this will list all the indexes for a specified table:

select * from sys.indexes
where object_id = (select object_id from sys.objects where name = 'MYTABLE')

This query will list all tables without an index:

SELECT name
FROM sys.tables 
WHERE OBJECTPROPERTY(object_id,'IsIndexed') = 0

And this is an interesting MSDN FAQ on a related subject:
Querying the SQL Server System Catalog FAQ

Upvotes: 134

nickf
nickf

Reputation: 546333

If you're using MySQL you can run SHOW KEYS FROM table or SHOW INDEXES FROM table

Upvotes: 44

Related Questions