protected virtual IList<IndexDefinition> ReadIndexes(string schemaName, string tableName)
{
const string query = @"SELECT OBJECT_SCHEMA_NAME(T.[object_id],DB_ID()) AS [Schema],
T.[name] AS [table_name], I.[name] AS [index_name], AC.[name] AS [column_name],
I.[type_desc], I.[is_unique], I.[data_space_id], I.[ignore_dup_key], I.[is_primary_key],
I.[is_unique_constraint], I.[fill_factor], I.[is_padded], I.[is_disabled], I.[is_hypothetical],
I.[allow_row_locks], I.[allow_page_locks], IC.[is_descending_key], IC.[is_included_column]
FROM sys.[tables] AS T
INNER JOIN sys.[indexes] I ON T.[object_id] = I.[object_id]
INNER JOIN sys.[index_columns] IC ON I.[object_id] = IC.[object_id]
INNER JOIN sys.[all_columns] AC ON T.[object_id] = AC.[object_id] AND IC.[column_id] = AC.[column_id]
WHERE T.[is_ms_shipped] = 0 AND I.[type_desc] <> 'HEAP'
AND T.object_id = OBJECT_ID('[{0}].[{1}]')
ORDER BY T.[name], I.[index_id], IC.[key_ordinal]";
DataSet ds = Read(query, schemaName, tableName);
DataTable dt = ds.Tables[0];
IList<IndexDefinition> indexes = new List<IndexDefinition>();
foreach (DataRow dr in dt.Rows)
{
List<IndexDefinition> matches = (from i in indexes
where i.Name == dr["index_name"].ToString()
&& i.SchemaName == dr["Schema"].ToString()
select i).ToList();
IndexDefinition iDef = null;
if (matches.Count > 0) iDef = matches[0];
// create the table if not found
if (iDef == null)
{
iDef = new IndexDefinition
{
Name = dr["index_name"].ToString(),
SchemaName = dr["Schema"].ToString(),
IsClustered = dr["type_desc"].ToString() == "CLUSTERED",
IsUnique = dr["is_unique"].ToString() == "1",
TableName = dr["table_name"].ToString()
};
indexes.Add(iDef);
}
// columns
ICollection<IndexColumnDefinition> ms = (from m in iDef.Columns
where m.Name == dr["column_name"].ToString()
select m).ToList();
if (ms.Count == 0)
{
iDef.Columns.Add(new IndexColumnDefinition
{
Name = dr["column_name"].ToString(),
Direction = dr["is_descending_key"].ToString() == "1" ? Direction.Descending : Direction.Ascending
});
}
}
return indexes;
}
