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

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)

  • Can I use this in my C# queries also. – Saravanan Dec 6 '11 at 13:33
  • Can you clarify what you mean by "C# queries"? – Mark Storey-Smith Dec 7 '11 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 Dec 8 '11 at 4:16
  • also tell me if there is any way to get the parameters in an sql query. – Saravanan Dec 8 '11 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; – Mark Storey-Smith Dec 8 '11 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, privacy policy and cookie policy

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