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