你的位置:首页 > 数据库

[数据库]查询数据库已存在的索引


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,          CColumnID=C.Column_id,          CColumnName=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        --  INNER JOIN -- 对于一个列包含多个索引的情况,只显示第1个索引信息 --  ( --    SELECT [object_id], Column_id, index_id=MIN(index_id) --    FROM sys.index_columns --    GROUP BY [object_id], Column_id --  ) IDXCUQ --    ON IDXC.[object_id]=IDXCUQ.[object_id] --      AND IDXC.Column_id=IDXCUQ.Column_id