Get Column names from a query without data

Question

I have a view vwGetData which gets data from two tables t1,t2 and has fields:

t1.Field1 [ALIAS1], t1.Field2, t2.Field3, t2.Field4, t2.Field5 [ALIAS5]

I will provide below input

Select * from vwGetData

i want to get below output in C#/SQL

ALIAS1
Field2
Field3
Field4
ALIAS5

or

ALIAS1, Field2, Field3, Field4, ALIAS5

I want to do this using C# and SQL.

Solution 1

The first thing you would do is make sure that no data gets returned:

SELECT TOP 0 [vwGetData].* FROM [vwGetData] WHERE 1 = 2;

Now assuming you know how to set up a DataReader you would do the following:

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 tableSchema = reader.GetSchemaTable();

  // Each row in the table schema describes a column
  foreach (DataRow row in tableSchema.Rows)
  {
    Console.WriteLine(row["ColumnName"]);
  }
}

You can also could also look into the SQL Catalog SYS Views.

Solution 2

SELECT COLUMN_NAME
FROM   
INFORMATION_SCHEMA.COLUMNS 
WHERE   
TABLE_NAME = 'vwGetData' 
ORDER BY 
ORDINAL_POSITION ASC; 

Solution 3

The easiest way I found is this.

using (SqlCommand command = new SqlCommand("SELECT * FROM vwGetData", conn))
{
    SqlDataReader reader = command.ExecuteReader();
    while (reader.Read())
    {
        for (int i = 0; i < reader.FieldCount; i++)
            Console.Writeline(reader.GetName(i));
    }
}

This will print the column names for each line of result you have.

Solution 4

There is a good sample here:

using System.Data;
using System.Data.OleDb;

OleDbConnection cn = new OleDbConnection();
OleDbCommand cmd = new OleDbCommand();
DataTable schemaTable; 
OleDbDataReader myReader; 

//Open a connection to the SQL Server Northwind database.
cn.ConnectionString = "Provider=SQLOLEDB;Data Source=server;User ID=login;
                       Password=password;Initial Catalog=Northwind";
cn.Open();

//Retrieve records from the Employees table into a DataReader.
cmd.Connection = cn;
cmd.CommandText = "SELECT * FROM Employees";
myReader = cmd.ExecuteReader(CommandBehavior.KeyInfo); 

//Retrieve column schema into a DataTable.
schemaTable = myReader.GetSchemaTable();

//For each field in the table...
foreach (DataRow myField in schemaTable.Rows){
    //For each property of the field...
    foreach (DataColumn myProperty in schemaTable.Columns) {
    //Display the field name and value.
    Console.WriteLine(myProperty.ColumnName + " = " + myField[myProperty].ToString());
    }
    Console.WriteLine();

    //Pause.
    Console.ReadLine();
}

//Always close the DataReader and connection.
myReader.Close();
cn.Close();

Solution 5

You can also load the data into a DataTable, like so:

DataTable dtTable = new DataTable();

using (SqlCommand command = new SqlCommand("SELECT * FROM Table", conn))
{
    SqlDataReader reader = command.ExecuteReader();
    dtTable.Load(reader);
}

And retrieve the column in the first row, like so:

var column = dtTable.Rows[0]["YourColumn"];

Or loop through all the rows and reference the column, like so:

foreach (var c in dtTable.AsEnumerable())
{
    var column = c["YourColumn"];
}

View more solutions

Comments

[Russ Cam]

they could really do with disposing of their resources in their sample code!

[Thakur]

this is what i needed TOP 0 does it :)

[Thakur]

does this get all the data (SELECT * FROM Employees) and then return the column info?

[Mark Kram]

you could also use SELECT TOP 0 [vwGetData].* FROM [vwGetData] WHERE 1 = 2;

[Admin]

I am beginner in C#. How do i use this query in the c# code block?

[Hamman Samuel]

Maybe it's my SQL Server version (2008) but none of the other proposed solutions actually work, not even the accepted answer. Only this one did, just FYI

[Ben Gribaudo]

What about using SET FMTONLY to turn off return of rows? This functionality is built into SQL for situations like this. Example: SET FMTONLY ON SELECT * FROM vwGetData SET FMTONLY OFF

[Rami]

Worked like a charm for me in a situation where pretty much all other solutions failed - so thanks a lot!

[Sterling Archer]

Could you elaborate more? What is going on here

[dovid]

instead WHERE 1 = 2, you can use command.ExecuteReader(CommandBehavior.SchemaOnly))