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!!!