sp_describe_first_result_set in MS SQL 2012
Introduction
When I am moving with MS SQL 2012 it surprised me every time. This time I have a new system stored procedure calledsp_describe_first_result_set. Which come with MS SQL Server 2012. Grade thing MS provide us.
So want is it
To understand any table object or view Meta data we always used system stored procedure sp_Help
To understand it lets take an example
Step-1 [ Create Base Table ]
IF OBJECT_ID(N'dbo.tbl_STUDENT', N'U') IS NOT NULL
BEGIN
DROP TABLE [dbo].[tbl_STUDENT];
END
GO
CREATE TABLE [dbo].[tbl_STUDENT]
(STDROLL INT NOT NULL IDENTITY PRIMARY KEY,
STDNAME VARCHAR(50) NOT NULL,
STDCLASS INT NOT NULL);
GO
Step-2 [ Insert Records ]
INSERT INTO [dbo].[tbl_STUDENT]
(STDNAME, STDCLASS)
VALUES ('Joydeep Das', 1),
('Rajesh Das', 1),
('Deepasree Das', 1),
('Dia Das', 1),
('Shipra Das', 1);
Step-3 [ Create Views ]
IF OBJECT_ID(N'dbo.view_STDDETALS', N'V') IS NOT NULL
BEGIN
DROP VIEW [dbo].[view_STDDETALS];
END
GO
CREATE VIEW [dbo].[view_STDDETALS]
AS
SELECT STDROLL, STDNAME, STDCLASS
FROM [dbo].[tbl_STUDENT];
GO
SELECT * FROM [dbo].[view_STDDETALS];
Step-4 [ Use sp_HELP ]
SP_HELP view_STDDETALS
Name Owner Type Created_datetime
view_STDDETALS dbo view 2013-12-27 16:39:54.880
Column_name Type Computed Length Prec Scale
STDROLL int no 4 10 0
STDNAME varchar no 50
STDCLASS int no 4 10 0
Here we not find any base table information
Step-5 [ Use sp_describe_first_result_set ]
Here we are table some example columns only to understand the example
EXEC sp_describe_first_result_set
N'SELECT * FROM view_STDDETALS', NULL, 0
name system_type_name max_length source_table
STDROLL int 4 NULL
STDNAME varchar(50) 50 NULL
STDCLASS int 4 NULL
Please look here we are taking parameter as 0. So in source_table it not shows anything it just showing the columns name and data type of the view.
EXEC sp_describe_first_result_set
N'SELECT * FROM view_STDDETALS', NULL, 1
name system_type_name max_length source_table
STDROLL int 4 tbl_STUDENT
STDNAME varchar(50) 50 tbl_STUDENT
STDCLASS int 4 tbl_STUDENT
Please look here we are taking parameter as 1. So in source_table it shows the base table name of the view not the view name.
EXEC sp_describe_first_result_set
N'SELECT * FROM view_STDDETALS', NULL, 2
name system_type_name max_length source_table
STDROLL int 4 view_STDDETALS
STDNAME varchar(50) 50 view_STDDETALS
STDCLASS int 4 view_STDDETALS
Please look here we are taking parameter as 2. So in source_table it shows the view name.
