Home>

sqlserver query table index,Do you know

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

Need to create an index Example:

Determine whether there are duplicate records based on a certain column.If the column is a non-primary key,Create index

Based on frequently asked columns,Create index

No need to create an index

The fields are mostly the same,For example:male, female

Don't create indexes on all columns,So when creating a new record,Increase maintenance overhead time.

oracle query user table index

select index_name, index_type, table_name from user_indexes where table_name="table name"

sqlserver query index on a 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" 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 "" end
from sys.indexes idx
inner join sys.index_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 i want to query

The above is all described in this article,Hope you like it.

  • Previous Detailed explanation of pointer structure array and pointer to structure variable in C ++
  • Next jQuery to achieve picture carousel special effects code sharing