C# 从 SQL Server 获取列名

Get column name from SQL Server

提问by Cristian Eduardo Lehuede Lyon

I'm trying to get the column names of a table I have stored in SQL Server 2008 R2.

我正在尝试获取存储在 SQL Server 2008 R2 中的表的列名。

I've literally tried everything but I can't seem to find how to do this.

我确实尝试了所有方法，但似乎无法找到如何执行此操作。

Right now this is my code in C#

现在这是我在 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 is returning 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 the <string>tags as this is how my web service sends the data.

它显示<string>标签，因为这是我的 Web 服务发送数据的方式。

采纳答案by Szymon

You can use the query below to get the column names for your table. The query below gets all the columns for a user table of a 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 that:

在您的代码中，它将如下所示：

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

回答by AnotherDeveloper

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

回答by M.Ali

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

回答by jcwrequests

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

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

这种类型的东西有很多内置的存储过程。

回答by user2880576

Currently, there are two ways I could think of doing this:

目前，我可以想到两种方法：

• In pure SQL Server SQL you can use the views defined in INFORMATION_SCHEMA.COLUMNS. There, you would need to select the row for your table, matching on the column TABLE_NAME.
• Since you are using C#, it's probably easier to obtain the names from the SqlDataReaderinstance that is returned by ExecuteReader. The class provides a property FieldCount, for the number of columns, and a method GetName(int), taking the column number as its argument and returning the name of the column.

• 在纯 SQL Server SQL 中，您可以使用INFORMATION_SCHEMA.COLUMNS. 在那里，您需要为表格选择行，匹配列TABLE_NAME。
• 由于使用的是C＃，它可能更容易从获得的名称SqlDataReader是由返回的实例ExecuteReader。该类提供了一个属性FieldCount，用于列数，和一个方法GetName(int)，将列号作为其参数并返回列的名称。

回答by Mark Kram

I typically use the GetSchema method to retrieve Column specific information, this snippet will return the column names in a string List:

我通常使用 GetSchema 方法来检索特定于列的信息，此代码段将返回字符串列表中的列名：

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

回答by Sierra

The original post was close to the goal, Just some small changes and you got it. Here is my solution.

原来的帖子接近目标，只是一些小的改动，你就明白了。这是我的解决方案。

   public List<string> GetColumns(string tableName)
    {
        List<string> colList = new List<string>();
        DataTable dataTable = new DataTable();


        string cmdString = String.Format("SELECT TOP 0 * FROM {0}", tableName);

        if (ConnectionManager != null)
        {
            try
            {
                using (SqlDataAdapter dataContent = new SqlDataAdapter(cmdString, ConnectionManager.ConnectionToSQL))
                {
                    dataContent.Fill(dataTable);

                    foreach (DataColumn col in dataTable.Columns)
                    {
                       colList.Add(col.ColumnName);
                    }
                }                   
            }
            catch (Exception ex)
            {
                InternalError = ex.Message;
            }
        }


        return colList;
    }