Query to check index on a table

Question

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

If you just need the indexed columns EXEC sp helpindex  TABLE NAME

User · Answer

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

User · Answer

Simply you can find index name and column names of a particular table using below command  SP HELPINDEX  tablename   It work s for me

User · Answer

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

User · Answer

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    ALL IND COLUMNS ALL INDEXES

User · Answer

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

User · Answer

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   lt YOUR TABLE NAME gt      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

[sql] Query to check index on a table

Examples related to sql

Examples related to sql-server-2008

Examples related to indexing