How can I get a list of the columns in a SQL SELECT statement?

I'm wanting to get a list of the column names returned from a SQL SELECT statement. Can someone suggest an easy way to do this?

I have a tool that lets users define a query using any SQL SELECT statement. The results of the query are then presented in a custom manner. To set up the presentation, I need to know the column names so that the user can store formatting settings about each column.

Btw, the formatting settings are all being created via ASP.NET web 开发者_如何转开发pages, so the query results will end up in .NET if that helps with any ideas people have.

Any ideas?

---

You should be able to do this using the GetName method. Something like this probably:

SqlDataReader mySDR = cmd.ExecuteReader();
for(int i = 0;i < mySDR.FieldCount; i++)
{
   Console.WriteLine(mySDR.GetName(i));
}

This is something you could do entirely from a asp.net page. No special/extra SQL required.

---

Assuming SQL Server: You could use SET FMTONLY to just return metadata (and not the actual data), e.g.:

USE AdventureWorks2008R2;
GO
SET FMTONLY ON;
GO
SELECT * 
FROM HumanResources.Employee;
GO
SET FMTONLY OFF;
GO

---

You can get by something as following

Note : You need to fill the DataTable of the Dataset.........

DataSet1 DataSet1 = new DataSet1();
DataTable dt = DataSet1.Tables(0);
DataColumn dc = null;

foreach (DataColumn dc_loopVariable in dt.Columns) {
    dc = dc_loopVariable;
    Response.write(dc.ColumnName.ToString() + " " + dc.DataType.ToString() + "<br>");
}

---

Another method to just return meta data is

select top 0 * from table

---

If you know the table name you could try using:

desc <table_name>

I'm assuming you are using SQL Server.

Or as an alternative:

SELECT COLUMN_NAME 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = 'TableNameGoesHere'
ORDER BY ORDINAL_POSITION

You might want to use the second option if you are going to be using ASP.NET

---

This will get you more than the column name if you need more information about each column like size, ordinal,etc. A few of the most important properties are listed, but there are more.

Note, DataObjects.Column is a POCO for storing column information. You can roll your own in your code. Also, note I derive the .Net type as well, useful for converting SQL data types to .Net (C#) ones. ConnectionString and TableName would be supplied from a caller.

            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                conn.Open();

                SqlCommand comm = new SqlCommand("Select top(1) * from " + TableName + " Where 1=0");
                comm.CommandType = CommandType.Text;
                comm.Connection = conn;
                using (SqlDataReader reader = comm.ExecuteReader(CommandBehavior.KeyInfo))
                {
                    DataTable dt = reader.GetSchemaTable();
                    foreach (DataRow row in dt.Rows)
                    {
                        //Create a column
                        DataObjects.Column column = new DataObjects.Column();

                        column.ColumnName = (string)row["ColumnName"];
                        column.ColumnOrdinal = (int)row["ColumnOrdinal"];
                        column.ColumnSize = (int)row["ColumnSize"];
                        column.IsIdentity = (bool)row["IsIdentity"];
                        column.IsUnique = (bool)row["IsUnique"];

                        //Get the C# type of data
                        object obj = row["DataType"];
                        Type runtimeType = obj.GetType();
                        System.Reflection.PropertyInfo propInfo = runtimeType.GetProperty("UnderlyingSystemType");
                        column.type = (Type)propInfo.GetValue(obj, null);

                        //Set a string so we can serialize properly later on
                        column.DataTypeFullName = column.type.FullName;

                        //I believe this is SQL Server Data Type
                        column.SQLServerDataTypeName = (string)row["DataTypeName"];

                        //Do something with the column
                    }
                }
            }