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);
}
