列出给定表的索引信息
需求描述
需求:查询出给定的表emp的索引信息。
解决方法:通过各个数据库里提供的与索引相关的数据字典进行查询。
注: 数据库数据集SQL脚本详见如下链接地址
SQL代码
-- Oracle:
CREATE INDEX Idx_EMP_EMPNO ON EMP(EMPNO);
SELECT table_name,index_name,column_name,column_position
FROM sys.all_ind_columns
WHERE table_name ='EMP'
AND table_owner='SHENL';
执行结果
-- Sql Server:
SELECT a.name AS table_name,
b.name AS index_name,
d.name AS column_name,
c.index_column_id
FROM sys.tables a,sys.indexes b,sys.index_columns c,sys.columns d
WHERE a.object_id= b.object_id
AND b.object_id = c.object_id
AND b.index_id = c.index_id
AND c.object_id = d.object_id
AND c.column_id = d.column_id
AND a.name = 'EMP'
执行结果
-- Mysql:
show index from emp;