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 called sp_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.

Hope you like it.

Posted by: MR. JOYDEEP DAS