1
sp_describe_first_result_set  @tsql=N'%s'

returns a lot of info. But I only want name and system_type_name.

Is it possible to specify the columns in the returned results?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Ben
  • 1,133
  • 1
  • 15
  • 30
  • 1
    No, not really. – Radu Gheorghiu Mar 03 '18 at 09:20
  • If there is any chance to use an *inline table valued function* instead, you should do this. This is faster, more reusable and much easier to consume. – Shnugo Mar 03 '18 at 11:02
  • What do you mean by using inline tvf, could you elaberate ? despite the sql part, I am quite familiar with sql server. – Ben Mar 03 '18 at 13:12
  • Possible duplicate of [SQL Server - SELECT FROM stored procedure](https://stackoverflow.com/questions/1492411/sql-server-select-from-stored-procedure) – Ben Mar 03 '18 at 14:05
  • I did more search, I guess the answer is not without lot of effort. Closing thanks for attention. – Ben Mar 03 '18 at 14:21

2 Answers2

1

You could first dump all columns in a temp table and select only the necessary columns from that.

create table #1 (name varchar(50), system_type_name varchar(50), col3 int, col4 int)
 insert into #1 (name, system_type_name, col3, col4)
        exec dbo.sp_describe_first_result_set
      select name, system_type_name from #1
  drop table #1
Steef
  • 303
  • 2
  • 11
1

To add on to @Steef's answer, below is the table definition matching the result set as documented:

DECLARE @results TABLE(
      is_hidden bit NOT NULL
    , column_ordinal int NOT NULL   
    , name sysname NULL
    , is_nullable bit NOT NULL
    , system_type_id int NOT NULL
    , system_type_name sysname NULL
    , max_length smallint NOT NULL
    , precision tinyint NOT NULL
    , scale tinyint NOT NULL    
    , collation_name sysname NULL
    , user_type_id int NULL
    , user_type_database sysname NULL
    , user_type_schema sysname NULL
    , user_type_name sysname NULL
    , assembly_qualified_type_name nvarchar(4000) NULL
    , xml_collection_id int NULL
    , xml_collection_database sysname NULL
    , xml_collection_schema sysname NULL
    , xml_collection_name sysname NULL
    , is_xml_document bit NOT NULL
    , is_case_sensitive bit NOT NULL
    , is_fixed_length_clr_type bit NOT NULL
    , source_server sysname NULL
    , source_database sysname NULL
    , source_schema sysname NULL
    , source_table sysname NULL 
    , source_column sysname NULL
    , is_identity_column bit  NULL
    , is_part_of_unique_key bit NULL
    , is_updateable bit  NULL
    , is_computed_column bit  NULL
    , is_sparse_column_set bit  NULL
    , ordinal_in_order_by_list smallint NULL
    , order_by_is_descending smallint NULL
    , order_by_list_length smallint NULL
    , tds_type_id int NOT NULL
    , tds_length int NOT NULL
    , tds_collation_id int NULL
    , tds_collation_sort_id tinyint NULL
);
Dan Guzman
  • 43,250
  • 3
  • 46
  • 71
  • thanks sir. I am trying to get the desc. of an arbitrary query, since I always have to create sth. I am turing to creating tempview>query form information_schema>teardown tempview, which is I was doing in pgsql. – Ben Mar 03 '18 at 14:35