sp_describe_first_result_set, new system stored procedure in SQL Server 2012

sp_describe_first_result_set, new system stored procedure in SQL Server 2012

I like SQL Server very much from the SQL Server 7.0. I used to follow each and every version of SQL Server very closely from its beta version. Microsoft always endeavors to make life easier and productive for developer and that is the spirit I like much.

I was busy in writing DBA related articles recently but now I think I have to give some fundamental updates about newer version of SQL Server to my blog reader and see, today I am here with one of the new system stored procedure “sp_describe_first_result_set” which is available only in SQL Server 2012 at the moment.

Before we further discuss “sp_describe_first_result_set”, let us create one dummy table with some sample rows so that we can use it in our example.

[sourcecode language=”sql”]CREATE TABLE tblResultSetTest

(

ID INT IDENTITY(1,1)

,BloggerName Varchar(25)

,BlogName VARCHAR(100)

,EmailAddress VARCHAR(100)

)

GO

INSERT INTO tblResultSetTest

SELECT ‘Ritesh Shah’,’Extreme-Advice.com’,’R.Shah@extreme-advice.com’ UNION ALL

SELECT ‘Pinal Dave’,’SQLAuthority.com’,’P.Dave@SQLAuthority.com’ UNION ALL

SELECT ‘Bihag Thaker’,’SQLArt.com’,’B.Thaker@SQLArt.com’

GO

CREATE VIEW ViewTblResultSetTest

AS

SELECT

ID as BlogID

,BloggerName as Name

,BlogName as Blog

,EmailAddress as Email

FROM

tblResultSetTest

GO [/sourcecode]

So, now we have one table “tblResultSetTest” with sample row and one view “viewTblResultSetTest” based on “tblResultsetTest” table. What would you do if I ask you to see metadata (Schema) of the table/view? Obviously, first thing would come in your mind is “SP_Help” system stored procedure which is one of the favorite system SP of any SQL Developer/DBA. Right?

You would do something like this:

[sourcecode language=”sql”]sp_help ViewTblResultSetTest [/sourcecode]

Which can give you result set like this:

.

Now, here twist comes, Microsoft has provided new system stored procedure “sp_describe_first_result_set” with much more functionality. Let us try it out by passing TSQL statement to this System stored Procedure, rather than table/view name we used to give in “SP_Help”

[sourcecode language=”sql”]EXEC sp_describe_first_result_set

N’SELECT * FROM ViewTblResultSetTest’, NULL, 0

GO [/sourcecode]

Last argument in above system stored procedure is “0” which is Browse_Information_Mode. “0” denotes that you will not be provided any details about table/view. You will only get column name which are part of our TSQL Statement (View) along with its datatype and few other view’s column related information. You can see the result in below given screen capture.

.

Now, let us execute same stored procedure with different Browse_Information_Mode.

[sourcecode language=”sql”]EXEC sp_describe_first_result_set

N’SELECT * FROM ViewTblResultSetTest’, NULL, 1

GO [/sourcecode]

Last argument in above system stored procedure is “1” which is Browse_Information_Mode. “1” provides you view’s column name and datatype as we have already received in “0” Browse_Information_Mode along with its original database name, schema name, original column name in table. You can see the result in below given screen capture.

.

Now, let us execute same stored procedure with different Browse_Information_Mode.

[sourcecode language=”sql”]EXEC sp_describe_first_result_set

N’SELECT * FROM ViewTblResultSetTest’, NULL, 2

GO [/sourcecode]

Last argument in above system stored procedure is “2” which is Browse_Information_Mode. “2” provides you view’s column name and datatype as we have already received in “0” Browse_Information_Mode along with its original database name but it will not provide original schema name and original column name of table, instead it will give you view name and column information from view itself. You can see the result in below given screen capture.

.

Isn’t it interesting??!!

Enjoy New SQL Server

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Quick Learn

Note: Microsoft Books online is a default reference of all articles.

Author: Riteshshah

Ritesh Shah is a database professional with having 16+ years of experience in various domain of IT field. He is a author of many technical articles on Microsoft Technology and authored a book of SQL Server 2012 Performance Tuning cookbook. He loves work with different Microsoft Products.

1 thought on “sp_describe_first_result_set, new system stored procedure in SQL Server 2012”

Comments are closed.