Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question

1 Answer 1

up vote 2 down vote accepted

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)

share|improve this answer
    
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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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