“sp_helptext“ – Useful command to view detail TIP #85

 

It might be already known to you but I thought for sharing because I frequently use this command and it is very useful command.

When someone wants to determine detail of a function or stored procedure he/she can use this useful command.

The syntax is very simple. Just write

sp_helptext Storedprocedure/ functionname

For example If I want to determine detail of a stored procedure “proc_FindStudentUsingCorrect” then I have to write following command

sp_helptext proc_FindStudentUsingCorrect

see below snap for detail

 

You can now copy the result text and check what exactly written in the stored procedure or function.

I hope you will use it in your day to day practice.

Enjoy!!!

RJ

How to determine Meta data or result set information of a stored procedure /trigger ? TIP # 73

 

Sometimes it may require that you don’t know what will be output of  a stored procedure ? what kind of result set it return ?

In such case SQL SERVER provided a new DMV statement which is sys.dm_exec_describe_first_result_set_for_object.

In other words if we want to know result set’s meta data then we can use it.

Lets understand this by an example.

Suppose we have an advertisementSelect stored procedure as shown below

As you see there are various column returning by the stored procedure.

Now let suppose we don’t have enough permission to view its definition or any other reason we are not able to view actual what is return in the stored procedure and now we want to know what is the result set then in such case we will use DMV command which is “SYS.DM_EXEC_DESCRIBE_FIRST_RESULT_SET_FOR_OBJECT”

We can use it as follows

SELECT *
FROM sys.dm_exec_describe_first_result_set_for_object
        (object_id('AdvertisementSelect'),0)

When we run it we will get result which we require as shown in below figure

If you see above screen you will find all the columns with their data type which will come as a result set of a stored procedure “AdvertisementSelect”

I hope this may help you somewhere.

Thanks

Rj!!!