Get column name from SQL Server

Question

I am trying to get the column names of a table that I saved in SQL Server 2008 R2.

I literally tried everything, but I can not find how to do it.

Now this is my code in C #

public string[] getColumnsName() { List<string> listacolumnas=new List<string>(); using (SqlConnection connection = new SqlConnection(Connection)) using (SqlCommand command = connection.CreateCommand()) { command.CommandText = "SELECT TOP 0 * FROM Usuarios"; connection.Open(); using (var reader = command.ExecuteReader(CommandBehavior.KeyInfo)) { reader.Read(); var table = reader.GetSchemaTable(); foreach (DataColumn column in table.Columns) { listacolumnas.Add(column.ColumnName); } } } return listacolumnas.ToArray(); } 

But this returns me the following

 <string>ColumnName</string> <string>ColumnOrdinal</string> <string>ColumnSize</string> <string>NumericPrecision</string> <string>NumericScale</string> <string>IsUnique</string> <string>IsKey</string> <string>BaseServerName</string> <string>BaseCatalogName</string> <string>BaseColumnName</string> <string>BaseSchemaName</string> <string>BaseTableName</string> <string>DataType</string> <string>AllowDBNull</string> <string>ProviderType</string> <string>IsAliased</string> <string>IsExpression</string> <string>IsIdentity</string> <string>IsAutoIncrement</string> <string>IsRowVersion</string> <string>IsHidden</string> <string>IsLong</string> <string>IsReadOnly</string> <string>ProviderSpecificDataType</string> <string>DataTypeName</string> <string>XmlSchemaCollectionDatabase</string> <string>XmlSchemaCollectionOwningSchema</string> <string>XmlSchemaCollectionName</string> <string>UdtAssemblyQualifiedName</string> <string>NonVersionedProviderType</string> <string>IsColumnSet</string> 

Any ideas?

It shows <string> tags since my web service is sending data.

Answer 1

You can use the query below to get the column names for your table. In the query below, all columns for a user table with the given name:

 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' 

---

In your code, it will look like this:

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

Answer 2

I usually use the GetSchema method to get information about the columns, this fragment returns the column names in the List row:

  using (SqlConnection conn = new SqlConnection("<ConnectionString>")) { string[] restrictions = new string[4] { null, null, "<TableName>", null }; conn.Open(); var columnList = conn.GetSchema("Columns", restrictions).AsEnumerable().Select(s => s.Field<String>("Column_Name")).ToList(); } 

Answer 3

 SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'YourTable' 

Answer 4

 public string[] getColumnsName() { List<string> listacolumnas=new List<string>(); using (SqlConnection connection = new SqlConnection(Connection)) using (SqlCommand command = connection.CreateCommand()) { command.CommandText = "select column_name from information_schema.columns where table_name = 'Usuarios'"; connection.Open(; using (var reader = command.ExecuteReader(CommandBehavior.KeyInfo)) { reader.Read(); var table = reader.GetSchemaTable(); foreach (DataColumn column in table.Columns) { listacolumnas.Add(column.ColumnName); } } } return listacolumnas.ToArray(); } 

Answer 5

 sp_columns - http://technet.microsoft.com/en-us/library/ms176077.aspx 

There are many built-in stored procedures for this type of thing.

Answer 6

Currently, I can think of it in two ways:

• In pure SQL Server SQL, you can use the views defined in INFORMATION_SCHEMA.COLUMNS . There you will need to select the row for your table corresponding to the column TABLE_NAME .
• Since you are using C #, it may be easier to get the names from the SqlDataReader instance that returns the ExecuteReader . The class provides the FieldCount property, the number of columns, and the GetName(int) method, taking the column number as an argument and returning the column name.