Query to check index on a table

I need a query to see if a table alread开发者_JAVA技巧y has any indexes on it.

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

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

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

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

SP_HELPINDEX 'tablename'

It works for me

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.

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.

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

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

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

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

for smss - mssql, one can refer the create script