Getting the metadata about a Query and a Stored procedure in SQL Server

Question

I would like to get all the names of the columns in a given query [SQL Textual Query] and also the columns that are in the SELECT clause in a Stored Procedure.

Is there any ways other than using REGEX to get these information from the SQL Server. I can give the name of the SP.

Kindly suggest me the best practice for getting this information while using SqlDataReader in C#.

Answer 1

SET FMTONLY ON can be used to return just column information, no rows will be processed or returned.

Returns only metadata to the client. Can be used to test the format of the response without actually running the query. (BOL)

Answer 2

Since this is being done in C# / .NET and not purely at the database level, there is a short-cut to SET FMTONLY ON by passing in a Command Behavior (optional parameter) of SchemaOnly to SqlCommand.ExecuteReader:

SqlDataReader _Reader = _Command.ExecuteReader(CommandBehavior.SchemaOnly);

Doing this means that you don't need to alter the query being submitted, hence this should be a "cleaner" approach :-).