Little bit of research given away these results. I have started with having a look at ForeignKey.FKTableSchema
Property and unfortunately not a .NET property. Later on things looked good when I found OleDbSchemaGuid.Indexes Field and everything looked good until I ran the application and got the Method is not supported by this provider. Eventually the following article lit up the way,
GetOleDbSchemaTable(OleDb.OleDbSchemaGuid.Indexes - How to access included columns on index
and this finding
The OleDb and Odbc providers do not provide a built-in catalog method
that will return non-key ("Included") index columns.
However it was some really interesting suggestion which let to writing this little Console application for you to harvest the indexes available in the table. This is achieved by directly querying the schema table from SQL. The following example is on Employees
table of famous Northwind
sample database. Here you go,
//Open a connection to the SQL Server Northwind database.
var connectionString =
"Provider=SQLOLEDB;Data Source=SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=SSPI;Encrypt=False;TrustServerCertificate=False";
using (var connection = new OleDbConnection(connectionString))
{
connection.Open();
var select = "SELECT " +
" T.name AS TABLE_NAME" +
" , IX.name AS INDEX_NAME" +
" , IC.index_column_id AS IX_COL_ID" +
" , C.name AS COLUMN_NAME" +
" , IC.is_included_column AS INCLUDED_NONKEY" +
" " +
"FROM " +
" sys.tables T " +
" INNER JOIN sys.indexes IX" +
" ON T.object_id = IX.object_id " +
" INNER JOIN sys.index_columns IC" +
" ON IX.object_id = IC.object_id " +
" AND IX.index_id = IC.index_id " +
" INNER JOIN sys.columns C" +
" ON IC.object_id = C.object_id " +
" AND IC.column_id = C.column_id " +
" " +
"WHERE T.name = 'Employees'" +
"ORDER BY IC.index_column_id";
OleDbCommand cmd = new OleDbCommand(@select, connection);
cmd.CommandType = CommandType.Text;
var outputTable = new DataSet("Table");
var my = new OleDbDataAdapter(cmd).Fill(outputTable);
foreach (DataTable table in outputTable.Tables)
{
foreach (DataRow myField in table.Rows)
{
//For each property of the field...
foreach (DataColumn myProperty in table.Columns)
{
//Display the field name and value.
Console.WriteLine(myProperty.ColumnName + " = " +
myField[myProperty].ToString());
}
Console.WriteLine();
}
}
}
Console.ReadLine();
and finally the results,
TABLE_NAME = Employees
INDEX_NAME = PK_Employees
IX_COL_ID = 1
COLUMN_NAME = EmployeeID
INCLUDED_NONKEY = False
TABLE_NAME = Employees
INDEX_NAME = LastName
IX_COL_ID = 1
COLUMN_NAME = LastName
INCLUDED_NONKEY = False
TABLE_NAME = Employees
INDEX_NAME = PostalCode
IX_COL_ID = 1
COLUMN_NAME = PostalCode
INCLUDED_NONKEY = False
However, later on by removing the restrictions I managed to get over the Method is not supported by this provider. error and ended up summing it up like this shorter solution,
//Open a connection to the SQL Server Northwind database.
var connectionString =
"Provider=SQLOLEDB;Data Source=SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=SSPI;Encrypt=False;TrustServerCertificate=False";
using (OleDbConnection cnn = new OleDbConnection(connectionString))
{
cnn.Open();
DataTable schemaIndexess = cnn.GetSchema("Indexes",
new string[] {null, null, null});
DataTable schemaIndexes = cnn.GetOleDbSchemaTable(
OleDbSchemaGuid.Indexes,
new object[] {null, null, null});
foreach (DataRow myField in schemaIndexes.Rows)
{
//For each property of the field...
foreach (DataColumn myProperty in schemaIndexes.Columns)
{
//Display the field name and value.
Console.WriteLine(myProperty.ColumnName + " = " +
myField[myProperty].ToString());
}
Console.WriteLine();
}
Console.ReadLine();
}
Which results to a longer output to sort out but part of it would be
TABLE_CATALOG = Northwind
TABLE_SCHEMA = dbo
TABLE_NAME = Employees
INDEX_CATALOG = Northwind
INDEX_SCHEMA = dbo
INDEX_NAME = LastName
PRIMARY_KEY = False
UNIQUE = False
CLUSTERED = False
TYPE = 1
FILL_FACTOR = 0
INITIAL_SIZE =
NULLS =
SORT_BOOKMARKS = False
AUTO_UPDATE = True
NULL_COLLATION = 4
ORDINAL_POSITION = 1
COLUMN_NAME = LastName
COLUMN_GUID =
COLUMN_PROPID =
COLLATION = 1
CARDINALITY =
PAGES = 1
FILTER_CONDITION =
INTEGRATED = False