SQL Server Portal

In SQL Server 2012 , Microsoft has provided a very helpful new system stored procedure namely “sp_describe_first_result_set”. Like name, like Work.. As the name goes, so is the performance. It gives the detailed meta data(Schema) information for the first possible result set. Note that it works only in SQL Server 2012. If you need to get the meta data info in SQL Server 2005/2008, you need to use “SP_help” and with very limited info availability.

Syntax :

sp_describe_first_result_set [ @tsql = ] N'Transact-SQL_batch'
[ , [ @params = ] N'parameters' ]
[ , [ @browse_information_mode = ]  ] ]

Lets make some simple examples to check its functionality  :

EXEC sp_describe_first_result_set @tsql =N'SELECT * FROM HumanResources.Department'
GO
--OUTPUT

As I discussed, you can view the  output as a detailed meta data.

Now, let me explain why “first possible result set,” name is given to this procedure, with the help of a simple example.
Given below are two queries and it will return two sets of result as well.

SELECT * FROM [HumanResources].[Department];
SELECT * FROM [HumanResources].[Employee];

But, if we pass both the queries to “sp_describe_first_result_set” to provide meta data, it will provide the meta data of the first query only and according to this functionality this name (sp_describe_first_result_set) is given to this stored procedure. Lets execute this scenario.

EXEC sp_describe_first_result_set @tsql =N'SELECT * FROM [HumanResources].[Department];
SELECT * FROM [HumanResources].[Employee];'
--Output

Now, lets look at the different option of @browse_information_mode in sp_describe_first_result_set

Lets create a view to explain the different option of @browse_information_mode.

Use AdventureWorks2012
GO
Create View [HumanResources].[vDepartment]
AS
SELECT [DepartmentID] as [ID]
,[Name] as [Department Name]
FROM [HumanResources].[Department]
--Output

Now, lets execute sp_describe_first_result_set with the different option of @browse_information_mode.

@browse_information_mode=0

Use AdventureWorks2012
GO
EXEC sp_describe_first_result_set
@tsql =N'SELECT * FROM [HumanResources].[vDepartment];'
,@params=NULL,@browse_information_mode=0
GO
-- When @browse_information_mode=0, it will give you the meta data but no source data available in this option.

@browse_information_mode=1

Use AdventureWorks2012
GO
EXEC sp_describe_first_result_set
@tsql =N'SELECT * FROM [HumanResources].[vDepartment];'
,@params=NULL,@browse_information_mode=1
GO
-- When @browse_information_mode=1, it will give you the meta data along with the source info but the source details will be based on this view's table.

@browse_information_mode=2

Use AdventureWorks2012
GO
EXEC sp_describe_first_result_set
@tsql =N'SELECT * FROM [HumanResources].[vDepartment];'
,@params=NULL,@browse_information_mode=2
GO
-- When @browse_information_mode=2, it will give you the meta data along with the source info but the source details will be based on this view.

Reference : MSDN