查询以检查表上的索引

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

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

9 个解决方案

#1

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系统目录常见问题解答

#2

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

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

#3

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.

大多数现代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

从INFORMATION_SCHEMA.TABLE_CONSTRAINTS中选择count（*）

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，它比他们的供应商特定的表更容易使用，所以只需使用它。

#4

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

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

#5

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.

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

#6

On Oracle:

在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_IND_COLUMNS
ALL_INDEXES
ALL_INDEXES

#7

First you check your table id (aka object_id)

首先检查你的表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

那么你可以获得列的名称。例如，假设您从先前的查询获得了数字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

#8

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

#9

-1

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

选择a.TABLE_CATALOG作为DB_name，a.TABLE_SCHEMA作为tbl_schema，a.TABLE_NAME作为tbl_name，a。 CONSTRAINT_NAME as constraint_name，来自INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE的b.CONSTRAINT_TYPE a.CONSTRAINT_NAME = b.CONSTRAINT_NAME上的加入INFORMATION_SCHEMA.TABLE_CONSTRAINTS b

#1