1

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#.

2 Answers 2

3

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)

5
  • Can I use this in my C# queries also.
    – Saravanan
    Commented Dec 6, 2011 at 13:33
  • Can you clarify what you mean by "C# queries"? Commented Dec 7, 2011 at 20:32
  • When i try to execute this code via C# in .NET, for the query BEGIN TRAN t1;SET FMTONLY ON; Select * from Users; SET FMTONLY OFF;COMMIT TRAN t1;, i am not getting the column metadata. I am just getting no values read through the SqlDataReader
    – Saravanan
    Commented Dec 8, 2011 at 4:16
  • also tell me if there is any way to get the parameters in an sql query.
    – Saravanan
    Commented Dec 8, 2011 at 5:49
  • The link to BOL in my answer shows the syntax to use, did you read it? You'll note it does not look like your example query. SET FMTONLY ON needs to be set on the connection before you issue they query i.e. SET FMTONLY ON; GO SELECT x FROM y; Commented Dec 8, 2011 at 10:54
1

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 :-).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.