How to get table column names at run time in C #?

Question

Suppose I have a table in SQLServer named MyTable

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

Now, if I need to insert any data into the table, I just just send the request “Insert query”, like this

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

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

Answer 1

You can use sql -

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

Answer 2

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); } } 

Answer 3

Another option using pure C # /. NET code: First, a helper method is used that returns a simple list of column names. Using a DataTable to store schema schema data means that other information can also be retrieved 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 the following:

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

Answer 4

Just with 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 

Answer 5

This question has been answered in different threads

check:

How to get column names from a table on SQL Server?

How to get column names from a table in Oracle?

How do I list all columns in a table?