Detailed SQL Server Query table index _mssql

SELECT Index name =a.name

, table name =c.name

, index field name =d.name

, index field position =d.colid

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--Check all user tables and  c.name= ' message '--check the specified table order by  C.name,a.name,d.name

You need to create an index for example:

To determine whether there is a duplicate record based on a column, or to create an index if it is not a primary key

Create an index based on a frequently queried column

No need to create an index

The contents of the field are mostly the same, for example: male, female

Do not create indexes on all columns, thus increasing the maintenance overhead time when creating new records.

Oracle Query User table index

Select Index_name,index_type,table_name from user_indexes where table_name= ' table name '

 sqlserver query for indexes on one table

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 ' if 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_du P_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, Fi Ll_factor=idx.fill_factor, padded=case when idx.is_padded=1 THEN n ' √ ' ELSE n ' "End" from sys.indexes IDX JOIN INNER Ex_columns IDXC on IDX. [OBJECT_ID]=IDXC.
[OBJECT_ID] and idx.index_id=idxc.index_id left JOIN sys.key_constraints KC on IDX. [object_id]=kc.[parent_object_id] and idx.index_id=kc.unique_index_id INNER JOIN sys.objects O on O.[object_id]=idx. [ObjeCT_ID] INNER JOIN sys.columns C on o.[object_id]=c.[object_id] and o.type= ' U ' and o.is_ms_shipped=0 and IDXC. column_id=c.column_id where o.name= ' cz201 '--cz201 is the table you want to query

The above content is described in all of this article, I hope you like it.