SQL:索引表索引

SQL SERVER 2008R2 

查询数据库表索引，不包含主约束索引

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36

-- 表索引，不包含主约束索引。  SELECT OBJ.OBJECT_ID AS [TABLE_ID],     OBJ.NAME AS [TABLE_NAME],   --  PK.name AS CONSTRAIN_NAME,     idx.name as index_name,     --索引类型： 聚集索引、非聚集索引     case WHEN idx.type = 1 then 'CLUSTERED'      when idx.type=2 then 'NONCLUSTERED'      ELSE '' END AS [INDEX_TYPE],     CASE WHEN IDX.is_unique=1 THEN  'UNIQUE'     WHEN IDX.is_unique=0 THEN '不唯一'     ELSE  '' END [IS_UNIQUE_DESC],    -- IDXC.index_column_id,     IDXC.key_ordinal,     COL.name AS COLUMN_NAME    -- ,IDX.*   FROM SYS.all_objects OBJ,      --  SYS.key_constraints PK,        sys.indexes idx,        sys.index_columns idxc,        SYS.all_columns COL   WHERE OBJ.type='U'   AND OBJ.name LIKE 'AP_Vouch'  -- AND OBJ.object_id = PK.parent_object_id  -- AND PK.type='PK'  -- and pk.parent_object_id = idx.object_id  -- and pk.unique_index_id = idx.index_id   and not exists ( select 1 from  SYS.key_constraints PK    where  pk.parent_object_id = idx.object_id     and pk.unique_index_id = idx.index_id )   and obj.object_id = idx.object_id   AND IDX.object_id = IDXC.object_id   and idx.index_id = idxc.index_id   AND IDXC.object_id = COL.object_id   and IDXC.column_id = COL.column_id --order by  OBJ.NAME, idx.name,IDXC.key_ordinal