sp_describe_undeclared_parameters & sp_describe_first_result_set

USE [AdventureWorks2012]
GO

EXEC sp_describe_first_result_set
N'SELECT * FROM Sales.SalesOrderDetail', NULL, 1
GO


CREATE VIEW dbo.MyView
AS
SELECT [SalesOrderID] soi_v
,[SalesOrderDetailID] sodi_v
,[CarrierTrackingNumber] stn_v
FROM [Sales].[SalesOrderDetail]
GO

/* Now let us execute above stored procedure with various options.
You can notice I am changing the very last parameter which I am passing to the stored procedure.
This option is known as for browse_information_mode. */

--when BrowseMode is set to 1 the resultset describes the details of the original source database, schema as well source table.
--When BrowseMode is set to 2 the resulset describes the details of the view as the source database.

EXEC sp_describe_first_result_set
N'SELECT soi_v soi,
sodi_v sodi,
stn_v stn
FROM MyView', NULL, 0;
GO

EXEC sp_describe_first_result_set
N'SELECT soi_v soi,
sodi_v sodi,
stn_v stn
FROM MyView', NULL, 1;
GO

EXEC sp_describe_first_result_set
N'SELECT soi_v soi,
sodi_v sodi,
stn_v stn
FROM MyView', NULL, 2;
GO

DROP VIEW MyView


-- It returns a result set that contains metadata about undeclared parameters in a Transact-SQL batch.

-- Build a query string that contains parameters
DECLARE @query NVARCHAR(MAX) = N'
SELECT
    object_id, name, type_desc
FROM sys.indexes
WHERE object_id = @id OR name = @name'

-- Retrieve metadata of @id and @name parameters
EXECUTE sp_describe_undeclared_parameters
    @tsql = @query