Selecting column names from a query NOT a view

Question

Is it possible to get column names from a query like you can get them from a view? The view example is below.. I cannot use the view example as these are queries created by users and will potentially be amended often..

SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('dbo.ViewName')

Thank you in advance..

Derek.

Answer 1

If you are on SQL Server 2012 or above, then you can use sp_describe_first_result_set.

The basic gist of this Stored Procedure involves passing the query you are running as a parameter. SQL Server then describes the results it will return, including column names, datatypes, size etc.

EXEC sp_describe_first_result_set N'SELECT * from my_table';

Answer 2

If you are using SQL2012 or higher, you can use sp_describe_first_result_set. For example:

sp_describe_first_result_set @tsql = N'EXEC dbo.usp_index'

with dbo.usp_index defined as:

CREATE PROCEDURE dbo.usp_index
AS 
    SELECT  object_id ,
          name ,
          type_desc
    FROM      sys.indexes

If you are using a version before 2012, you can achieve something similar with a CLR procedure:

[Microsoft.SqlServer.Server.SqlProcedure]
public static void usp_describe_first_result_set(string sqlCommandText)
{
    using (SqlConnection connection = new SqlConnection("context connection=true"))
    {
        connection.Open();
        SqlCommand cmd = new SqlCommand();
        DataTable schemaTable;
        SqlDataReader myReader;

        //Retrieve records from the Employees table into a DataReader.
        cmd.Connection = connection;
        cmd.CommandText = sqlCommandText;
        myReader = cmd.ExecuteReader(CommandBehavior.SingleResult);


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

        //For each field in the table...
        foreach (DataRow myField in schemaTable.Rows)
        {
            int column = 0;

            SqlDataRecord dataRow = new SqlDataRecord(
              new SqlMetaData("name", SqlDbType.VarChar, 256),
              new SqlMetaData("column_ordinal", SqlDbType.VarChar, 256),
              new SqlMetaData("max_length ", SqlDbType.VarChar, 256),
              new SqlMetaData("precision", SqlDbType.VarChar, 256),
              new SqlMetaData("scale", SqlDbType.VarChar, 256),
              new SqlMetaData("is_unique", SqlDbType.VarChar, 256),
              new SqlMetaData("is_key ", SqlDbType.VarChar, 256),
              new SqlMetaData("source_server", SqlDbType.VarChar, 256),
              new SqlMetaData("source_database", SqlDbType.VarChar, 256),
              new SqlMetaData("source_column", SqlDbType.VarChar, 256),
              new SqlMetaData("source_schema ", SqlDbType.VarChar, 256),
              new SqlMetaData("source_table", SqlDbType.VarChar, 256),
              new SqlMetaData("is_nullable ", SqlDbType.VarChar, 256),
              new SqlMetaData("is_aliased ", SqlDbType.VarChar, 256),
              new SqlMetaData("is_computed_column ", SqlDbType.VarChar, 256),
              new SqlMetaData("is_identity_column", SqlDbType.VarChar, 256),
              new SqlMetaData("is_autoincrement ", SqlDbType.VarChar, 256),
              new SqlMetaData("is_rowversion ", SqlDbType.VarChar, 256),
              new SqlMetaData("is_hidden ", SqlDbType.VarChar, 256),
              new SqlMetaData("is_long ", SqlDbType.VarChar, 256),
              new SqlMetaData("is_readonly ", SqlDbType.VarChar, 256),
              new SqlMetaData("system_type_name", SqlDbType.VarChar, 256),
              new SqlMetaData("xml_collection_database", SqlDbType.VarChar, 256),
              new SqlMetaData("xml_collection_schema", SqlDbType.VarChar, 256),
              new SqlMetaData("xml_collection_name", SqlDbType.VarChar, 256),
              new SqlMetaData("is_column_set ", SqlDbType.VarChar, 256));


            //For each property of the field...
            foreach (DataColumn myProperty in schemaTable.Columns)
            {
                //Display the field name and value.
                if (!myProperty.ColumnName.Contains("Provider") && !myProperty.ColumnName.Contains("Assembly") && !myProperty.ColumnName.Equals("DataType"))
                {
                    dataRow.SetValue(column, myField[myProperty].ToString());

                    column++;
                }
            }

            if (rowCount == 0)
            {
                SqlContext.Pipe.SendResultsStart(dataRow);
            }

            SqlContext.Pipe.SendResultsRow(dataRow);

            rowCount++;
        }


        SqlContext.Pipe.SendResultsEnd();
    }
}