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?

Spiny

My suggested solution is not limited to MS Access databases. – Sena

Answer 1

this will work on sql server 2005 and up:

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

Landre

@Gold, add "access" tag to question! – Landre

This is not working in access. Can you explain what INFORMATION_SCHEMA is? – Chavis

@Uzair Ali, I answered this question before the OP specified that they were working in Access Database. My answer if specifically for SQL Server 2005 and up and databases that support the information schema ANSI standard. Apparently your version of Access Database does not support the information schema ANSI standard. The information schema is an ANSI standard set of read-only views which provide information about all of the tables, views, columns, and procedures in a database. en.wikipedia.org/wiki/Information_schema – Landre

Answer 2

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>

Sena

Isn't 'conn.Close()' redundant, since the connection is created inside a 'using' construct, which will close the connection when execution exists the construct anyway? – Redwood

Answer 3

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.

Algonquin

If all you need is column names then this quick and dirty approach is fine IMO. Note you can add WHERE 0=1 (or similar) to ensure no data is returned. – Ahriman

This doesn't work if you have captions defined. It shows the caption instead. – Kattie

Answer 4

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'

Polliwog

How does your answer apply to Access/Jet? – Resistencia

Sorry, I take back my -1, the original question wasn't clear on that point. – Kikuyu

I think when this answer was posted there was only a SQL tag, the MS-Access tag was added later. – Ahriman

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.

Kikuyu

From C#, the ADO Catalog functions might be easier. – Resistencia

IIRC the calls to OpenSchema to get the INFORMATION SCHEMA VIEWs are not straightforward for C# and may not be worth it just for column names. – Ahriman

Answer 6

This Code will print all column name of a table as a class with getter property of all column names which can be then used in c# code

    declare @TableName sysname = '<EnterTableName>'
    declare @Result varchar(max) = 'public class ' + @TableName + '
    {'

    select @Result = @Result + '
        public static string ' + ColumnName + ' { get { return "'+ColumnName+'"; } }
    '
    from
    (
        select
            replace(col.name, ' ', '_') ColumnName,
            column_id ColumnId
        from sys.columns col
            join sys.types typ on
                col.system_type_id = typ.system_type_id AND col.user_type_id = typ.user_type_id
        where object_id = object_id(@TableName)
    ) t
    order by ColumnId

    set @Result = @Result  + '
    }'

    print @Result

Output:

 public class tblPracticeTestSections
 {
   public static string column1 { get { return "column1"; } }

   public static string column2{ get { return "column2"; } }

   public static string column3{ get { return "column3"; } }

   public static string column4{ get { return "column4"; } }

 }

Treadwell

Answer 7

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

Ratline

MSysObjects contains the list of tables, there's no equivalent for fields unfortunately. – Gentilis

Answer 8

for microsoft SQL in c# you can do the following:

Dictionary<string, int> map = 
(from DataRow row in Schema.Rows
 let columnName = (string)row["ColumnName"]
  select columnName)
 .Distinct(StringComparer.InvariantCulture)
 .Select((columnName, index) => new { Key = columnName, Value = index })
 .ToDictionary(pair => pair.Key, pair => pair.Value);

thus creates a map of column name into its index which can be used as follows:

internal sealed class ColumnToIndexMap
{
    private const string NameOfColumn = "ColumnName";
    private DataTable Schema { get; set; }
    private Dictionary<string, int> Map { get; set; }

    public ColumnToIndexMap(DataTable schema)
    {
        if (schema == null) throw new ArgumentNullException("schema");
        Schema = schema;

        Map = (from DataRow row in Schema.Rows
               let columnName = (string)row[NameOfColumn]
               select columnName)
              .Distinct(StringComparer.InvariantCulture)
              .Select((columnName, index) => new { Key = columnName, Value = index })
              .ToDictionary(pair => pair.Key, pair => pair.Value);
    }

    int this[string name]
    {
        get { return Map[name]; }
    }

    string this[int index]
    {
        get { return Schema.Rows[index][NameOfColumn].ToString(); }
    }
}

Telophase

Answer 9

I have had good luck with the GetSchema property of the OleDb.Connection:

A class to provide column data. This returns ALL columns in the database. The resulting DataTable can then be filtered by column names which correspond (mostly) to those found in a standard INFORMATION_SCHEMA (which MS Access does NOT provide for us):

    class JetMetaData
    {
        /// <summary>
        /// Returns a datatable containing MetaData for all user-columns
        /// in the current JET Database. 
        /// </summary>
        /// <returns></returns>
        public static DataTable AllColumns(String ConnectionString)
        {
            DataTable dt;

            using (OleDbConnection cn = new OleDbConnection(ConnectionString))
            {
                cn.Open();
                dt = cn.GetSchema("Columns");
                cn.Close();
            }
            return dt;
        }

    }

Then, Consuming the class in a rather crude and not-so-elegant example, and filtering on TABLE_NAME:

    private void Form1_Load(object sender, EventArgs e)
    {
        DataTable dt = JetMetaData.AllColumns("", Properties.Settings.Default.JetConnection);
        String RowFilter = "TABLE_NAME = 'YourTableName'";
        DataView drv = dt.DefaultView;
        drv.RowFilter = RowFilter;

        DataGridView dgv = this.dataGridView1;

        dgv.DataSource = drv;

    }

Note that I do not pretend that this is all well-though out code. It is only an example. But I have used something like this on a number of occasions, and in fact even created an application to script out an entire MS Access database (contraints and all) using similar methods.

While I have seen others in this thread mention the get Schema, it seem slike some of the implementation was overly complicated . . .

Hope that helps!

Hypersensitize