private void AddIndex(DbTable table, PropertyInfo prop) { var indexAttr = GetAttribute <IndexAttribute>(prop); if (indexAttr == null) { return; } var index = table.Indexes.SingleOrDefault(x => x.Name == indexAttr.Name); if (index == null) { index = new DbIndex { Name = indexAttr.Name, Schema = table.Schema, Table = table.Name, IsUnique = indexAttr.IsUnique, }; table.Indexes.Add(index); } index.Columns.Add(prop.Name); }
private List <DbIndex> GetDatabaseIndexes() { //REF: http://stackoverflow.com/questions/765867/list-of-all-index-index-columns-in-sql-server-db var indexes = _db.Query <dynamic>(@" SELECT schema_name(schema_id) as SchemaName, OBJECT_NAME(si.object_id) as TableName, si.name as IndexName, (CASE is_primary_key WHEN 1 THEN 'PK' ELSE '' END) as PK, (CASE is_unique WHEN 1 THEN '1' ELSE '0' END)+' '+ (CASE si.type WHEN 1 THEN 'C' WHEN 3 THEN 'X' ELSE 'B' END)+' '+ -- B=basic, C=Clustered, X=XML (CASE INDEXKEY_PROPERTY(si.object_id,index_id,1,'IsDescending') WHEN 0 THEN 'A' WHEN 1 THEN 'D' ELSE '' END)+ (CASE INDEXKEY_PROPERTY(si.object_id,index_id,2,'IsDescending') WHEN 0 THEN 'A' WHEN 1 THEN 'D' ELSE '' END)+ (CASE INDEXKEY_PROPERTY(si.object_id,index_id,3,'IsDescending') WHEN 0 THEN 'A' WHEN 1 THEN 'D' ELSE '' END)+ (CASE INDEXKEY_PROPERTY(si.object_id,index_id,4,'IsDescending') WHEN 0 THEN 'A' WHEN 1 THEN 'D' ELSE '' END)+ (CASE INDEXKEY_PROPERTY(si.object_id,index_id,5,'IsDescending') WHEN 0 THEN 'A' WHEN 1 THEN 'D' ELSE '' END)+ (CASE INDEXKEY_PROPERTY(si.object_id,index_id,6,'IsDescending') WHEN 0 THEN 'A' WHEN 1 THEN 'D' ELSE '' END)+ '' as 'Type', INDEX_COL(schema_name(schema_id)+'.'+OBJECT_NAME(si.object_id),index_id,1) as Key1, INDEX_COL(schema_name(schema_id)+'.'+OBJECT_NAME(si.object_id),index_id,2) as Key2, INDEX_COL(schema_name(schema_id)+'.'+OBJECT_NAME(si.object_id),index_id,3) as Key3, INDEX_COL(schema_name(schema_id)+'.'+OBJECT_NAME(si.object_id),index_id,4) as Key4, INDEX_COL(schema_name(schema_id)+'.'+OBJECT_NAME(si.object_id),index_id,5) as Key5, INDEX_COL(schema_name(schema_id)+'.'+OBJECT_NAME(si.object_id),index_id,6) as Key6 FROM sys.indexes as si LEFT JOIN sys.objects as so on so.object_id=si.object_id WHERE index_id>0 -- omit the default heap and OBJECTPROPERTY(si.object_id,'IsMsShipped')=0 -- omit system tables and not (schema_name(schema_id)='dbo' and OBJECT_NAME(si.object_id)='sysdiagrams') -- omit sysdiagrams ORDER BY SchemaName,TableName,IndexName "); var list = new List <DbIndex>(); foreach (var x in indexes) { var index = new DbIndex { Name = x.IndexName, Schema = x.SchemaName, Table = x.TableName, }; if (x.Key1 != null) { index.Columns.Add(x.Key1); } if (x.Key2 != null) { index.Columns.Add(x.Key2); } if (x.Key3 != null) { index.Columns.Add(x.Key3); } if (x.Key4 != null) { index.Columns.Add(x.Key4); } if (x.Key5 != null) { index.Columns.Add(x.Key5); } if (x.Key6 != null) { index.Columns.Add(x.Key6); } list.Add(index); } return(list); }