How to get column names of table at runtime in C#?

Question

Lets say I have a table in SQLServer named MyTable

ID FirstName   LastName
1  Harry       Dan
2  Maria       Vicente
3  Joe         Martin

Now if I have to insert any data in table, I will simply fire Insert Query like this

INSERT INTO MyTable (ID, FirstName, LastName) VALUES (4, Smith, Dan);

But what if I don't know the column names beforehand, I only know table name. Then is there a way to get the column name of table at runtime?

You are inserting data into tables that you don't know the structure until runtime? Sounds like a bad scenario IMHO. — Jake1164, Apr 23 '13 at 11:43
@Jake1164, this is quite common for fully disconnected systems. Consider an example where you have a centralized data store that is used as a facilitator to share data between geographically dispersed services that you know nothing about. Now consider you could send an object, leverage its properties, and build an `INSERT` statement for a table you know nothing about - all you know is that the properties match the schema and that the table is **supposed** to be there. ***In short, I can think of more scenarios where you wouldn't know about the schema.*** — Mike Perrenoud, Apr 23 '13 at 12:30
In addition to all the answers about querying system metadata, if you prefer a 'pure' .NET solution then you could use an [SMO `Table` object](http://msdn.microsoft.com/en-us/library/ms220470.aspx) and examine the `Columns` collection. — Pondlife, Apr 23 '13 at 13:00

score 16 · Accepted Answer · answered Apr 23 '13 at 11:40

16

You can use sql-

SELECT name FROM sys.columns WHERE object_id = OBJECT_ID('TABLE_NAME')

answered Apr 23 '13 at 11:40

Anton Baksheiev

2,211
2
14
15

score 7 · Answer 2 · answered Apr 23 '13 at 11:40

Or you can query for SELECT TOP 0 * FROM TableName. Then, you can get the columns:

using(var reader = command.ExecuteReader())
{
    reader.Read();
    var table = reader.GetSchemaTable();
    foreach (DataColumn column in table.Columns)
    {
        Console.WriteLine(column.ColumnName);
    }
 }

score 5 · Answer 3 · answered Oct 07 '14 at 15:15

Another option using pure C# / .NET code: First a helper method, that here returns a simple list of column names. Using a DataTable to hold table schema information, means that other information can also be retreived for each column, fx. if it is an AutoIncreament column etc.

    private IEnumerable<string> GetColumnNames(string conStr, string tableName)
    {
        var result = new List<string>();
        using (var sqlCon = new SqlConnection(conStr))
        {
            sqlCon.Open();
            var sqlCmd = sqlCon.CreateCommand();
            sqlCmd.CommandText = "select * from " + tableName + " where 1=0";  // No data wanted, only schema
            sqlCmd.CommandType = CommandType.Text;

            var sqlDR = sqlCmd.ExecuteReader();
            var dataTable = sqlDR.GetSchemaTable();

            foreach (DataRow row in dataTable.Rows) result.Add(row.Field<string>("ColumnName"));
        }

        return result;
    }

The method can be called as:

        var sortedNames = GetColumnNames("Data Source=localhost;Initial Catalog=OF2E;Integrated Security=SSPI", "Articles").OrderBy(x => x);
        foreach (var columnName in sortedNames) Console.WriteLine(columnName);

Seems like people have a hard time noticing, that the question for a C# solution and not a SQL answer (though, it is a bit confusing, as the question includes some SQL code) :) :) — Niels Peter Gibe, Feb 23 '17 at 17:18

score 1 · Answer 4 · answered Apr 23 '13 at 11:44

1

Simply by this Query :

SELECT * FROM sys.columns
WHERE object_id = OBJECT_ID('dbo.[table_name]')

OR This Query :

SELECT COLUMN_NAME
FROM information_schema.columns
WHERE TABLE_NAME = [table_name]
ORDER BY COLUMN_NAME

answered Apr 23 '13 at 11:44

Obama

2,586
2
30
49

score 0 · Answer 5 · answered Jul 12 '18 at 13:39

var ColName = "";
        var model = new AttributeMappingSource().GetModel(typeof(DataClassesDataContext));
        foreach (var mt in model.GetTables())
        {
            if (mt.TableName == "dbo.Table_Name")
            {
                foreach (var dm in mt.RowType.DataMembers)
                {
                    ColName = dm.MappedName + ", ";
                    Response.Write(ColName);
                }
            }
        }

score -1 · Answer 6 · edited May 23 '17 at 12:02

-1

This question was answered before in various threads

check:

How can I get column names from a table in SQL Server?

How can I get column names from a table in Oracle?

How do I list all the columns in a table?

edited May 23 '17 at 12:02

Community

1
1

answered Apr 23 '13 at 11:45

ClotzA

161
7

How to get column names of table at runtime in C#?

6 Answers6