详解sqlserver查询表索引
SELECT 索引名称=a.name
,表名=c.name
,索引字段名=d.name
,索引字段位置=d.colid
FROMsysindexesa JOINsysindexkeysbONa.id=b.idANDa.indid=b.indid JOINsysobjectscONb.id=c.id JOINsyscolumnsdONb.id=d.idANDb.colid=d.colid WHEREa.indidNOTIN(0,255) --andc.xtype='U'andc.status>0--查所有用户表 ANDc.name='message'--查指定表 ORDERBYc.name,a.name,d.name
需创建索引例如：
根据某列判断是否有重复记录，如果该列为非主键，则创建索引
根据经常查询的列，创建索引
无须创建索引
字段内容大部分一样，例如：男，女
不要给所有的列都创建索引，这样在创建新记录时，增加维护开销时间。
Oracle查询用户表索引
selectindex_name,index_type,table_namefromuser_indexeswheretable_name='表名'
SqlServer查询一个表上的索引
SELECT TableId=O.[object_id], TableName=O.Name, IndexId=ISNULL(KC.[object_id],IDX.index_id), IndexName=IDX.Name, IndexType=ISNULL(KC.type_desc,'Index'), Index_Column_id=IDXC.index_column_id, ColumnID=C.Column_id, ColumnName=C.Name, Sort=CASEINDEXKEY_PROPERTY(IDXC.[object_id],IDXC.index_id,IDXC.index_column_id,'IsDescending') WHEN1THEN'DESC'WHEN0THEN'ASC'ELSE''END, PrimaryKey=CASEWHENIDX.is_primary_key=1THENN'√'ELSEN''END, [UQIQUE]=CASEWHENIDX.is_unique=1THENN'√'ELSEN''END, Ignore_dup_key=CASEWHENIDX.ignore_dup_key=1THENN'√'ELSEN''END, Disabled=CASEWHENIDX.is_disabled=1THENN'√'ELSEN''END, Fill_factor=IDX.fill_factor, Padded=CASEWHENIDX.is_padded=1THENN'√'ELSEN''END FROMsys.indexesIDX INNERJOINsys.index_columnsIDXC ONIDX.[object_id]=IDXC.[object_id] ANDIDX.index_id=IDXC.index_id LEFTJOINsys.key_constraintsKC ONIDX.[object_id]=KC.[parent_object_id] ANDIDX.index_id=KC.unique_index_id INNERJOINsys.objectsO ONO.[object_id]=IDX.[object_id] INNERJOINsys.columnsC ONO.[object_id]=C.[object_id] ANDO.type='U' ANDO.is_ms_shipped=0 ANDIDXC.Column_id=C.Column_idwhereO.name='cz201'--cz201是你要查询的表
以上内容就是本文全部所述，希望大家喜欢。