详解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.indidNOT IN（0,255） -- andc.xtype="U"andc.status>0 --查所有用户表 ANDc.name="message" --查指定表 ORDER BYc.name,a.name,d.name

需创建索引例如：
根据某列判断是否有重复记录，如果该列为非主键，则创建索引

根据经常查询的列，创建索引
无须创建索引
字段内容大部分一样，例如：男，女
不要给所有的列都创建索引，这样在创建新记录时，增加维护开销时间。
Oracle 查询用户表索引

select index_name,index_type,table_name from user_indexes where table_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=CASE INDEXKEY_PROPERTY（IDXC.[object_id],IDXC.index_id,IDXC.index_column_id,"IsDescending"）WHEN 1 THEN "DESC" WHEN 0 THEN "ASC" ELSE "" END,PrimaryKey=CASE WHEN IDX.is_primary_key=1 THEN N"√"ELSE N"" END,[UQIQUE]=CASE WHEN IDX.is_unique=1 THEN N"√"ELSE N"" END,Ignore_dup_key=CASE WHEN IDX.ignore_dup_key=1 THEN N"√"ELSE N"" END,Disabled=CASE WHEN IDX.is_disabled=1 THEN N"√"ELSE N"" END,Fill_factor=IDX.fill_factor,Padded=CASE WHEN IDX.is_padded=1 THEN N"√"ELSE N"" ENDFROM sys.indexes IDX INNER JOIN sys.index_columns IDXCON IDX.[object_id]=IDXC.[object_id]AND IDX.index_id=IDXC.index_idLEFT JOIN sys.key_constraints KCON IDX.[object_id]=KC.[parent_object_id]AND IDX.index_id=KC.unique_index_idINNER JOIN sys.objects OON O.[object_id]=IDX.[object_id]INNER JOIN sys.columns CON O.[object_id]=C.[object_id]AND O.type="U"AND O.is_ms_shipped=0AND IDXC.Column_id=C.Column_id where O.name="cz201" --cz201是你要查询的表

以上内容就是本文全部所述，希望大家喜欢。