public override IList <IndexInfo> GetIndexInfo(string tableName)
{
var result = new List <IndexInfo>();
using (var cn = GetMsSqlConnection())
{
var cmdText =
"SELECT" +
" ind.name AS indexName," +
" col.name AS columnName," +
" ind.is_unique AS isUnique," +
" ind.is_primary_key AS isPrimary" +
" FROM sys.indexes ind" +
" INNER JOIN sys.index_columns ic" +
" ON ind.object_id = ic.object_id" +
" AND ind.index_id = ic.index_id" +
" INNER JOIN sys.columns col" +
" ON ic.object_id = col.object_id" +
" AND ic.column_id = col.column_id" +
" INNER JOIN sys.tables t" +
" ON ind.object_id = t.object_id" +
" WHERE" +
" t.name='{0}' AND" +
//" ind.is_unique_constraint = 0 AND" +
" t.is_ms_shipped = 0" +
" ORDER BY t.name, ind.name, ind.index_id, ic.index_column_id";
var table = new DataTable("IndexList");
cmdText = Format(cmdText, tableName);
var command = new SqlCommand(cmdText, cn);
var adapter = new SqlDataAdapter(command);
adapter.Fill(table);
IndexInfo iiCurrent = null;
string keyNameCurrent = null;
foreach (DataRow row in table.Rows)
{
var keyName = row[0] as string;
var isPrimary = (bool)row[3];
var isUnique = (bool)row[2];
if (keyName != keyNameCurrent)
{
iiCurrent = new IndexInfo(isPrimary, isUnique);
result.Add(iiCurrent);
keyNameCurrent = keyName;
}
iiCurrent?.AddColumn(row[1] as string);
}
}
return(FilterUniqueIndexes(result));
}
public override IList <IndexInfo> GetIndexInfo(string tableName)
{
var result = new List <IndexInfo>();
using (var cn = GetMySqlConnection())
{
var table = new DataTable("IndexList");
var command = new MySqlCommand("SHOW INDEXES FROM " + tableName, cn);
var adapter = new MySqlDataAdapter(command);
adapter.Fill(table);
IndexInfo iiCurrent = null;
string keyNameCurrent = null;
foreach (DataRow row in table.Rows)
{
var keyName = row[2] as string;
var isPrimary = keyName == "PRIMARY";
//var isUnique = (long)row[1] == 0;
var isUnique = Convert.ToInt64(row[1]) == 0;
if (keyName != keyNameCurrent)
{
iiCurrent = new IndexInfo(isPrimary, isUnique);
result.Add(iiCurrent);
keyNameCurrent = keyName;
}
iiCurrent?.AddColumn(row[4] as string);
}
}
// Use GroupBy to get Distinct entries (so we can use lambda)
// Duplicate keys would generate compile errors
//return result.GroupBy(
// i => Join(Empty, i.Columns),
// (key, group) => group.First())
// .ToList()
// .AsReadOnly();
return(FilterUniqueIndexes(result));
}
