SqlServer，Oracle，db2，MySql查询表索引

提示：扫一扫查出行
【扫一扫了解最新限行尾号】
复制提示

SqlServer
1. 查询表索引

SELECT

     索引名称=a.name

     ,表名=c.name

     ,索引字段名=d.name

     ,索引字段位置=d.colid

     ,c.status

  FROM sysindexes a

     JOIN sysindexkeys b ON a.id=b.id AND a.indid=b.indid

     JOIN sysobjects c ON b.id=c.id

     JOIN syscolumns d ON b.id=d.id AND b.colid=d.colid

WHERE a.indid NOT IN(0,255)

     and c.xtype='U' --and c.status>0 --查所有用户表

     AND c.name='message' --查指定表

ORDER BY c.name,a.name,d.name;

SqlServer
2. 查询没有索引的表

select * from sysobjects where xtype='U'

   and name not in (

   SELECT c.name

   FROM sysindexes a

   JOIN sysindexkeys b ON a.id=b.id AND a.indid=b.indid

   JOIN sysobjects c ON b.id=c.id

   JOIN syscolumns d ON b.id=d.id AND b.colid=d.colid

   WHERE a.indid NOT IN(0,255)

	and c.xtype='U' --and c.status>0 --查所有用户表

	--AND c.name='message' --查指定表

)

order by name

Oracle

select user_ind_columns.index_name,user_ind_columns.column_name,

user_ind_columns.column_position,user_indexes.uniqueness

from user_ind_columns,user_indexes

where user_ind_columns.index_name = user_indexes.index_name

and user_ind_columns.table_name = ‘你想要查询的表名字’;

db2

SELECT

     SUBSTR(SI.INDSCHEMA, 1, 30) AS INDSCHEMA,

     SUBSTR(SI.INDNAME, 1, 30) AS INDNAME,

     MGI.INDEX_SCANS,

     MGI.INDEX_ONLY_SCANS

FROM

    TABLE(MON_GET_INDEX('EPRICER', 'CTMTTRN', -2)) as MGI,

    SYSCAT.INDEXES AS SI

WHERE

    MGI.TABSCHEMA = SI.TABSCHEMA

    AND MGI.TABNAME = SI.TABNAME

    AND MGI.IID = SI.IID

ORDER BY

    MGI.INDEX_SCANS DESC;

MySql