how to get all details about a mysql table using c#?

Question

Hi! I want to retrieve these info for a mysql table using c#

1) Complete column definitions including name, size and data type, and extra info like null/not null, unsigned,auto increament, default values, if data type is enum, the accepted values

2) All constraints - Primary/Foreign/Check/Unique

3) All indexes

I can get column related basic info using "describe table_name" query against the database.

but how to fetch all these info?

regards, Anjan

Answer 1

I think Describe table_name does that. Not sure though.

Answer 2

You could use the information schema tables.

Answer 3

Hi,

just throw queries against INFORMATION_SCHEMA...

For example, to get column definitions:

SELECT   TABLE_NAME
       , COLUMN_NAME
       , DATA_TYPE
       , CHARACTER_MAXIMUM_LENGTH
       , CHARACTER_OCTET_LENGTH 
       , NUMERIC_PRECISION 
       , NUMERIC_SCALE AS SCALE
       , COLUMN_DEFAULT
       , IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS

Take a look at the information schema tables to get additional info.

Hope it helps.