How to return column names from SQL database

Question

Question says it all really, I just want the all column names from a table, I'm looking for as basic way to do this as possible.

You can query the database schema as suggested [here](https://stackoverflow.com/questions/1054984/how-can-i-get-column-names-from-a-table-in-sql-server) — rango, Oct 15 '17 at 17:28
Possible duplicate of [How can I get column names from a table in SQL Server?](https://stackoverflow.com/questions/1054984/how-can-i-get-column-names-from-a-table-in-sql-server) — Ben Cottrell, Oct 15 '17 at 17:28
None of the 16 anwers in the link is what OP most probably want (see answer of pim), so I do not see it as a duplicate. — Antonín Lejsek, Oct 15 '17 at 17:51
Not a duplicate. This question is tagged C# as well as SQL, the "Possible Duplicate" is SQL only. — DDuffy, Apr 07 '20 at 11:01

pim · Accepted Answer · 2017-10-15T17:28:43.940

If you run the following SQL you'll get an empty rowset. From which you can interpret the column names by using a SqlCommand and DataReader.

using (var conn = new SqlConnection("your_conn_string"))
{
    var command = new SqlCommand("select * from [dbo].[tableName] where 1 = 2");
    conn.Open();

    using(var dr = command.ExecuteReader())
    {
        var columns = new List<string>();

        for(int i=0;i<reader.FieldCount;i++)
        {
           columns.Add(reader.GetName(i));
        }
    }
}

score 0 · Answer 2 · answered Oct 15 '17 at 17:26

0

That depends entirely on the database. Almost all databases have some sort of metadata about the database. Most implement some version of the information_schema method.

For instance, a common way to get information about columns is:

select column_name
from information_schema.columns
where table_name = ? and table_schema = ?;

? are place-holders for the table name and schema.

answered Oct 15 '17 at 17:26

Gordon Linoff

1,242,037
58
646
786

1

He is asking how to do it using C# – OctoCode Oct 15 '17 at 17:32

score 0 · Answer 3 · answered Nov 27 '17 at 03:03

This should do what you want.

public string[] getColumnsName()
{
    List<string> listacolumnas=new List<string>();
    using (SqlConnection connection = new SqlConnection(Connection))
        using (SqlCommand command = connection.CreateCommand())
        {
            command.CommandText = "select c.name from sys.columns c inner join sys.tables t on t.object_id = c.object_id and t.name = 'Usuarios' and t.type = 'U'";
            connection.Open();
            using (var reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    listacolumnas.Add(reader.GetString(0));
                }
            }
        }
    return listacolumnas.ToArray();
}

Get column name from SQL Server

How to return column names from SQL database

3 Answers3