SQL 查询以检查表上的索引

Query to check index on a table

提问by sine

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

我需要一个查询来查看一个表是否已经有任何索引。

回答by gkrogers

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

在 SQL Server 上，这将列出指定表的所有索引：

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

这是有关相关主题的有趣 MSDN 常见问题解答：
查询 SQL Server 系统目录常见问题解答

回答by nickf

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

如果您使用的是 MySQL，您可以运行SHOW KEYS FROM table或SHOW INDEXES FROM table

回答by Salim

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

如果您只需要索引列 EXEC sp_helpindex 'TABLE_NAME'

回答by Donnie

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

大多数现代 RDBMS 都支持该INFORMATION_SCHEMA模式。如果您的支持，那么您想要INFORMATION_SCHEMA.TABLE_CONSTRAINTS或INFORMATION_SCHEMA.KEY_COLUMN_USAGE，或者两者都想要。

To see if yours supports it is as simple as running

看看你的是否支持它就像运行一样简单

select count(*) from INFORMATION_SCHEMA.TABLE_CONSTRAINTS

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.

编辑：SQL Server 确实有INFORMATION_SCHEMA，而且它比供应商特定的表更易于使用，所以只需使用它。

回答by FerranB

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
  

• 确定表上的所有索引：

  SELECT index_name 
   FROM user_indexes
   WHERE table_name = :table
  

• 确定列索引和索引上的列：

  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

• ALL_IND_COLUMNS
• ALL_INDEXES

回答by Michael Potter

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:

这是我用于 TSQL 的内容，它解决了我的表名可能包含模式名称和数据库名称的问题：

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.

这个用例是我想要一个命名表的索引列表，这样我就可以编写一个过程来动态压缩表上的所有索引。

回答by Weapon X

First you check your table id (aka object_id)

首先，您检查您的表 ID（又名 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

然后你可以得到列的名称。例如，假设您从先前的查询中获得数字 4 作为 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

回答by Tejasvi Hegde

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

创建了一个存储过程来列出 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

回答by SunilBk

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.

也检查一下 这给出了跨数据库的关联约束的概述。还请包括使用感兴趣的表名来促进 where 条件，以便更快地提供信息。

   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