retrieving index info
-
I am writing a utility to copy tables from one DB to another. This can be from MySQL, MS SQL Server, MS Access DB, or SQLite DB to another DB of one of the above types. The program handles any of the above combinations. For example, the user can choose to copy a table from MySQL server to an MS Access database. I am using ADO.NET to write this app. Currently I create a DataReader using the query "Select * from ", and then call its GetSchemaTable() method to obtain the field info of the source table. This method returns a DataTable that contains each field's basic info such as field name, type, size, whether or not it is a key, etc. These allow me to do the data type match and create the fields in the target DB. My question is how to obtain the source table's index info. The above DataTable object seems not sufficient for this purpose. Can you give me a pointer as to how this can be done? Thanks!
-
I am writing a utility to copy tables from one DB to another. This can be from MySQL, MS SQL Server, MS Access DB, or SQLite DB to another DB of one of the above types. The program handles any of the above combinations. For example, the user can choose to copy a table from MySQL server to an MS Access database. I am using ADO.NET to write this app. Currently I create a DataReader using the query "Select * from ", and then call its GetSchemaTable() method to obtain the field info of the source table. This method returns a DataTable that contains each field's basic info such as field name, type, size, whether or not it is a key, etc. These allow me to do the data type match and create the fields in the target DB. My question is how to obtain the source table's index info. The above DataTable object seems not sufficient for this purpose. Can you give me a pointer as to how this can be done? Thanks!
There's no unified API for that. You will have to implement it for each of those database systems individually. For SQL-Server you could either query the INFORMATION_SCHEMA or use SQL Server Management Objects (SMO). For MS Access there are also queryable schema-tables (MSys_xxx). No idea regarding the other database systems though, would have to ask Google.