SELECTTableId=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=CASEINDEXKEY_PROPERTY(IDXC.[object_id],IDXC.index_id,IDXC.index_column_id,\'IsDescending\')WHEN1THEN\'DESC\'WHEN0THEN\'ASC\'ELSE\'\'END,PrimaryKey=CASEWHENIDX.is_primary_key=1THENN\'√\'ELSEN\'\'END,[UQIQUE]=CASEWHENIDX.is_unique=1THENN\'√\'ELSEN\'\'END,Ignore_dup_key=CASEWHENIDX.ignore_dup_key=1THENN\'√\'ELSEN\'\'END,Disabled=CASEWHENIDX.is_disabled=1THENN\'√\'ELSEN\'\'END,Fill_factor=IDX.fill_factor,Padded=CASEWHENIDX.is_padded=1THENN\'√\'ELSEN\'\'ENDFROMsys.indexesIDXINNERJOINsys.index_columnsIDXCONIDX.[object_id]=IDXC.[object_id]ANDIDX.index_id=IDXC.index_idLEFTJOINsys.key_constraintsKCONIDX.[object_id]=KC.[parent_object_id]ANDIDX.index_id=KC.unique_index_idINNERJOINsys.objectsOONO.[object_id]=IDX.[object_id]INNERJOINsys.columnsCONO.[object_id]=C.[object_id]ANDO.type=\'U\'ANDO.is_ms_shipped=0ANDIDXC.Column_id=C.Column_id--INNERJOIN--对于一个列包含多个索引的情况,只显示第1个索引信息--(--SELECT[object_id],Column_id,index_id=MIN(index_id)--FROMsys.index_columns--GROUPBY[object_id],Column_id--)IDXCUQ--ONIDXC.[object_id]=IDXCUQ.[object_id]--ANDIDXC.Column_id=IDXCUQ.Column_id
|
