From C#, how to find the names of the columns of a table in SQL Server?

Question

I can find and display all names of all tables in a listbox.

But I need display column names of selected table from listbox by click a button.

My function :

public void GetTableNames()
{
    string strConnect = "Data Source=;Initial Catalog=DATA;User ID=sa;Password=***";
    using (SqlConnection con = new SqlConnection(strConnect))
    {
        con.Open();

        using (SqlCommand com = new SqlCommand(@"SELECT * FROM INFORMATION_SCHEMA.TABLES
                                                 WHERE TABLE_TYPE='BASE TABLE'  
                                                 ORDER BY TABLE_NAME ASC ", con))
        {
            using (SqlDataReader reader = com.ExecuteReader())
            {
                listBox2.Items.Clear();
                int counter = 0;
                while (reader.Read())
                {
                    counter++;
                    listBox2.Items.Add((string)reader["TABLE_NAME"]);
                }

                lblTablesCount.Text = counter.ToString();
            }
        }
    }
}

Call function in button

private void button1_Click(object sender, EventArgs e)     
{
    GetTableNames();
}

My question : Given a selected table, how can I find the names of the columns of that table? The user picks the table from a listbox and clicks a button.

[`INFORMATION_SCHEMA.COLUMNS`](http://technet.microsoft.com/en-us/library/ms188348.aspx) perhaps? Or maybe [`sys.objects`](http://technet.microsoft.com/en-us/library/ms190324.aspx). — user, Feb 07 '14 at 22:15
`select column_name from information_schema.columns` `where table_name = @TableName` `order by ordinal_position` — CJBS, Feb 07 '14 at 22:16

Steve · Answer 1 · 2014-02-07T22:32:52.710

Just search in INFORMATION_SCHEMA.COLUMNS

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @yourtablename




public List<string> GetColumnNames(string tableName)
{
    List<string> columns = new List<string>();
    string strConnect = ".........";
    using (SqlConnection con = new SqlConnection(strConnect))
    {
         con.Open();
         using (SqlCommand com = new SqlCommand(@"SELECT COLUMN_NAME 
                                 FROM INFORMATION_SCHEMA.COLUMNS 
                                 WHERE TABLE_NAME = @yourtablename", con))
         {

             com.Parameters.AddWithValue("@yourtableName", tableName);
             using (SqlDataReader reader = com.ExecuteReader())
             {
                 columns.Add(reader["COLUMN_NAME"].ToString());
             }
         }
    }
    return columns;
}

now, in the calling method add the returned columns list to your user interface object for display

private void button2_Click(object sender, EventArgs e)
{
     string tableName = comboBox1WithTable.SelectedItem.ToString();
     List<string> cols = GetColumnNames(tableName);
     comboBox2WithColumns.DataSource = cols;
}

score 2 · Answer 2 · answered Feb 07 '14 at 22:17

2

SELECT c.name AS ColName
FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
WHERE t.name = @SelTableName

Just provide the selected table name from the dropdownlist to this SQL script.

answered Feb 07 '14 at 22:17

randcd

2,263
1
19
21

score 1 · Answer 3 · answered Feb 07 '14 at 22:17

To get all column names you just need to run the following SQL Command after you have the table name.

private void GetColumnNames(string tableName)
{
    string query = "SELECT * FROM myDatabase.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName";
    using (var conn = new SqlConnection(myConnectionString))
    using (var cmd = new SqlCommand(query, conn))
    {
        cmd.Parameters.AddWithValue("@TableName", tableName)
        conn.Open();
        var reader = cmd.ExecuteReader();
        //Store the contents of reader in a variable to update your list box.
    }
}

score 1 · Accepted Answer · answered Feb 07 '14 at 22:31

You need to provide also the database-name + schema-name + table-name:

string table = string.Format("{0}.{1}.{2}", reader[0], reader[1], reader[2]); 
listBox2.Items.Add(table);

Then you can handle the SelectedIndexChanged event of the ListBox. Here you can use SqlCommand.ExecuteReader(CommandBehavior.SchemaOnly) to retrieve the schema informations only. DataReader.GetSchemaTable creates a DataTable with the column-metadata:

private void listBox2_SelectedIndexChanged(object sender, EventArgs e)
{
    string table = listBox2.SelectedItem.ToString();
    using (SqlConnection con = new SqlConnection(strConnect))
    {
        con.Open();
        using (SqlCommand com = new SqlCommand(@"SELECT * FROM " + table, con))
        {
            using (SqlDataReader reader = com.ExecuteReader(CommandBehavior.SchemaOnly))
            {
                listBoxColumns.Items.Clear();
                DataTable schemaTable = reader.GetSchemaTable();
                foreach(DataRow colRow in schemaTable.Rows)
                    listBoxColumns.Items.Add(colRow.Field<String>("ColumnName"));
            }
        }
    }
}

Sir Thank you so much.Solved by your answer.How can I create script table query of the selected table from listBox2 (by SelectedIndexChanged or by click o button ) Is it possible ? Can you give to me some tips ? — user3107343, Feb 07 '14 at 23:14
Right click on table->Script Table as -> Query of table.I mean that.Is it possible apply this in these codes ? — user3107343, Feb 07 '14 at 23:16
@user3107343: maybe this helps: http://stackoverflow.com/questions/21547/in-sql-server-how-do-i-generate-a-create-table-statement-for-a-given-table — Tim Schmelter, Feb 07 '14 at 23:19

From C#, how to find the names of the columns of a table in SQL Server?

4 Answers4