How can I get the field names of a database table?

Question

How can I get the field names of an MS Access database table?

Is there an SQL query I can use, or is there C# code to do this?

Answer 1

Depending on the DB engine your using you can easily query the DB system tables for that information

For access i can't find the answer i know you can see the sys tables in access and from there you could try and determine where that information is but im not really sure how to do this part. tried using an example but got nowwhere

Answer 2

Are you asking how you can get the column names of a table in a Database?

If so it completely depends on the Database Server you are using.

In SQL 2005 you can select from the INFORMATION_SCHEMA.Columns View

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'MyTable'

IN SQL 2000 you can join SysObjects to SysColumns to get the info

SELECT     
    dbo.sysobjects.name As TableName
    , dbo.syscolumns.name AS FieldName
FROM
    dbo.sysobjects 
    INNER JOIN dbo.syscolumns 
         ON dbo.sysobjects.id = dbo.syscolumns.id
WHERE
    dbo.sysobjects.name = 'MyTable'

Answer 3

this will work on sql server 2005 and up:

select * from INFORMATION_SCHEMA.COLUMNS 
where TABLE_Name='YourTableName'
order by ORDINAL_POSITION

Answer 4

Run this query:

select top 1 *
From foo

and then walk the list fields (and returned values) in the result set to get the field names.

Answer 5

Use the DAO automation classes. You may already have an interop library for it in your Visual Studio installation. If not, it's easy enough to create one; just add a reference to the DAO COM library.

using dao;
...
DBEngineClass dbengine = new DBEngineClass();
dbengine.OpenDatabase(path, null, null, null);
Database database = dbengine.Workspaces[0].Databases[0];
List<string> fieldnames = new List<string>();
TableDef tdf = database.TableDefs[tableName];
for (int i = 0; i < tdf.Fields.Count; i++)
{
    fieldnames.Add(tdf.Fields[i].Name);
}
database.Close();
dbengine.Workspaces[0].Close();

This is just as easy as querying a system table (which I've found to be problematic in Access), and you can get a lot of additional information this way.

EDIT: I've modified the code from what I posted yesterday, which I had just translated from VB.NET, and which was missing a couple of pieces. I've rewritten it and tested it in C# in VS2008.

Answer 6

Use IDataReader.GetSchemaTable()

Here's an actual example that accesses the table schema and prints it plain and in XML (just to see what information you get):

class AccessTableSchemaTest
{
    public static DbConnection GetConnection()
    {
        return new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=..\\Test.mdb");
    }

    static void Main(string[] args)
    {
        using (DbConnection conn = GetConnection())
        {
            conn.Open();

            DbCommand command = conn.CreateCommand();
            // (1) we're not interested in any data
            command.CommandText = "select * from Test where 1 = 0";
            command.CommandType = CommandType.Text;

            DbDataReader reader = command.ExecuteReader();
            // (2) get the schema of the result set
            DataTable schemaTable = reader.GetSchemaTable();

            conn.Close();
        }

        PrintSchemaPlain(schemaTable);

        Console.WriteLine(new string('-', 80));

        PrintSchemaAsXml(schemaTable);

        Console.Read();
    }

    private static void PrintSchemaPlain(DataTable schemaTable)
    {
        foreach (DataRow row in schemaTable.Rows)
        {
            Console.WriteLine("{0}, {1}, {2}",
                row.Field<string>("ColumnName"),
                row.Field<Type>("DataType"),
                row.Field<int>("ColumnSize"));
        }
    }

    private static void PrintSchemaAsXml(DataTable schemaTable)
    {
        StringWriter stringWriter = new StringWriter();
        schemaTable.WriteXml(stringWriter);
        Console.WriteLine(stringWriter.ToString());
    }
}

Points of interest:

1. Don't return any data by giving a where clause that always evaluates to false. Of course this only applies if you're not interested in the data :-).
2. Use IDataReader.GetSchemaTable() to get a DataTable with detailed info about the actual table.

For my test table the output was:

ID, System.Int32, 4
Field1, System.String, 50
Field2, System.Int32, 4
Field3, System.DateTime, 8
--------------------------------------------------------------------------------
<DocumentElement>
  <SchemaTable>
    <ColumnName>ID</ColumnName>
    <ColumnOrdinal>0</ColumnOrdinal>
    <ColumnSize>4</ColumnSize>
    <NumericPrecision>10</NumericPrecision>
    <NumericScale>255</NumericScale>
    <DataType>System.Int32, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089</DataType>
    <ProviderType>3</ProviderType>
    <IsLong>false</IsLong>
    <AllowDBNull>true</AllowDBNull>
    <IsReadOnly>false</IsReadOnly>
    <IsRowVersion>false</IsRowVersion>
    <IsUnique>false</IsUnique>
    <IsKey>false</IsKey>
    <IsAutoIncrement>false</IsAutoIncrement>
  </SchemaTable>
  [...]
</DocumentElement>