Cannot get column names after returning first table from stored procedure in pymssql

Question

I have a procedure in sql server that returns multiple tables:

create procedure newtest
as 
begin
   select 1 as a
   select 2 as b
end

In python, cursor.description simply returns the name of the first column: a

I want to get every column name in every table.

How can i do this?

This is my code:

cur.execute(com)
                num_tables=0
                while True:
                    print(cur.description)
                    ret=cur.fetchall()   

                    if len(ret)>0: 
                        ret_list.append(ret)
                        num_tables+=1
                        print(ret)                       
                    else:
                        break

Answer 1

If the command returns multiple tables (i.e. multiple result sets). You can use Cursor.nextset () to switch from one set to the next.

Sort of:

num_tables = 0
while True:
    print(cur.description)

    # all lines of the current set
    ret = cur.fetchall()

    if len(ret) > 0:
        ret_list.append(ret)
        num_tables += 1
        print(ret)

    # check and fetch the next set
    if not cur.nextset():
        break

Result sets must not have the same number of columns. For instance:

create procedure newtest
as 
begin
   select 1 as a
   select 2 as b, 3 as c
end

Result:

(('a', <class 'int'>, None, 10, 10, 0, False),)
[(1, )]
(('b', <class 'int'>, None, 10, 10, 0, False), ('c', <class 'int'>, None, 10, 10, 0, False))
[(2, 3)]