Wednesday, November 19, 2014

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

FROM sys.dm_exec_describe_first_result_set_for_object

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.



No comments:

Post a Comment