Get column names using aliases

Question

I'm using SQL Server 2008 and I have a SP that looks like this:

SELECT id = t.nTableId
       ,t.nTableId AS id2
       ,t.nTableId
FROM   dbo.table AS t

I'm using a .NET application where I want the column names: id, id2 and nTableId.

Right now I'm using sp_helptext to get the SP as a list of strings

var columns = new List<string>();
using (var reader = cmd.ExecuteReader())
{
    while (reader.Read())
    {
        columns.Add(reader.GetString(0));
    }
}

In this list of strings I'm trying to find SELECT and FROM, and then take the rows between these two. These rows do I format so that it looks correct afterwards.

Then problem is that I don't find this solution good enough. Do you guys have any tips that would work for SQL Server 2008 to retrieve the column names id, id2 and nTableId in this case.

[`sp_describe_first_result_set`](http://msdn.microsoft.com/en-us/library/ff878602.aspx) might be what you're looking for, and doesn't involve a whole heap of string manipulation. — Damien_The_Unbeliever, Jan 05 '15 at 11:17
http://stackoverflow.com/questions/681653/can-you-get-the-column-names-from-a-sqldatareader — Richard Hansell, Jan 05 '15 at 11:25
@RichardHansell So much effort from my side and there it was. I will check it out! — MrProgram, Jan 05 '15 at 11:28
@SriramSakthivel Because I'm using this in a application to create a XML file based on the column names — MrProgram, Jan 05 '15 at 11:28
@Damien_The_Unbeliever This won't work in sql server 2008 :) — MrProgram, Jan 05 '15 at 11:29
[`SET FMTONLY`](http://msdn.microsoft.com/en-us/library/ms173839.aspx) was the older variant of the same concept. — Damien_The_Unbeliever, Jan 05 '15 at 11:52

score 1 · Accepted Answer · edited Dec 18 '19 at 15:59

1

var columns = new List<string>();
DbCommand cmd = cnn.CreateCommand("SELECT * FROM Table1",  CommandType.Text);
DataTable Dt= cnn.GetDataTable(cmd);
foreach (System.Data.DataColumn col in dt.Columns)
{
    columns.Add(col.ColumnName);
}

edited Dec 18 '19 at 15:59

elmer007

1,412
14
27

answered Jan 06 '15 at 06:28

JAIGANESH

87
4

Vipul · Answer 2 · 2015-01-06T08:37:03.947

It will be a 2 step process. First fire a query employing your stored procedure so that no data is returned. like :

SELECT * FROM OPENQUERY(LOCALSERVER, 'EXEC your_stored_procedure') WHERE 1=2

Since the condition will not hold true ever, hence no data will be returned. We need to use OPENQUERY (mainly used for linked servers) because we want to dynamically modify the stored procedure query.

Now you can extract table schema of the empty resultset using your reader

using(var reader = command.ExecuteReader())
{
      // This will return false - we don't care, we just want to make sure the schema table is there.
         reader.Read();
         var table = reader.GetSchemaTable();
         foreach (DataColumn column in table.Columns)
         {
             Console.WriteLine(column.ColumnName);
         }
 }

Get column names using aliases

2 Answers2