How to find number of columns returned from query in powershell

Question

Post reply

How to find number of columns returned from query in powershell

Chand00

SSCertifiable

Points: 6466
More actions
July 25, 2014 at 2:21 am

#397115

I want to find out number of columns returned from a query in a powershell script.
As we will be passing a dynamic query which can retrieve only 2 columns or 5 columns or n columns based on user's choice.
$SQLQuery = " select * from sysdatabases "
$QueryResults = Invoke-Sqlcmd -Query $SQLQuery -ServerInstance Win7 -SuppressProviderContextWarning
foreach ($row in $QueryResults)
{
$row.Item(0)
}
Can you please help on how to find number of columns returned from above query.
[font="Verdana"]Thanks
Chandra Mohan[/font]

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

Gary Varga SSC Guru Points: 82166 More actions · Answer 1

I am sorry but I am away from SQL Server access but isn't the following valid:

$QueryResults.Columns.Count

Gaz

-- Stop your grinnin' and drop your linen...they're everywhere!!!

Chand00 SSCertifiable Points: 6466 More actions · Answer 2

I tried it already does not work

$QueryResults.Columns.Count

or

$QueryResults.Items.Count

is not working

[font="Verdana"]Thanks
Chandra Mohan[/font]

Gary Varga SSC Guru Points: 82166 More actions · Answer 3

What is the type of $QueryResults? It should be available by typing:

$QueryResults.GetType().FullName

Gaz

-- Stop your grinnin' and drop your linen...they're everywhere!!!

Chand00 SSCertifiable Points: 6466 More actions · Answer 4

System.Object[]

[font="Verdana"]Thanks
Chandra Mohan[/font]

Gary Varga SSC Guru Points: 82166 More actions · Answer 5

Does the following help:

$QueryResults.Count

as it is just an array of objects?

Edit: Because everyone makes mistakes 😉

Gaz

-- Stop your grinnin' and drop your linen...they're everywhere!!!

Chand00 SSCertifiable Points: 6466 More actions · Answer 6

It gives only the number of rows but what I want is number of columns

[font="Verdana"]Thanks
Chandra Mohan[/font]

Gary Varga SSC Guru Points: 82166 More actions · Answer 7

Well I suppose if we have a row then we might be able to get somewhere but if we have an array of an array of items that is different. In order to check then you can do the following:

$QueryResults[0].GetType().FullName

If we have an array of arrays then we can do the following:

$QueryResults[0].Count

Otherwise if we have a DataRow then we can do the following:

$QueryResults[0].Table.Columns.Count

Otherwise I would need to think again.

Gaz

-- Stop your grinnin' and drop your linen...they're everywhere!!!

Gary Varga SSC Guru Points: 82166 More actions · Answer 8

Chandra, please can you indicate which one resolved the issue as I am sure that someone with your question will read our conversation and wonder what was the right option. Thanks.

Gaz

-- Stop your grinnin' and drop your linen...they're everywhere!!!

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 9

Chand00 (7/25/2014)
I want to find out number of columns returned from a query in a powershell script.
As we will be passing a dynamic query which can retrieve only 2 columns or 5 columns or n columns based on user's choice.
$SQLQuery = " select * from sysdatabases "
$QueryResults = Invoke-Sqlcmd -Query $SQLQuery -ServerInstance Win7 -SuppressProviderContextWarning
foreach ($row in $QueryResults)
{
$row.Item(0)
}
Can you please help on how to find number of columns returned from above query.

My question would be... WHY is it necessary to do this in PowerShell instead of T-SQL?? :blink:

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Chand00 SSCertifiable Points: 6466 More actions · Answer 10

Hi,

Thank you. Below one worked for me.

$QueryResults[0].Table.Columns.Count

Apologies. I tried to post but I was unable to post somehow. Once again Thankyou

[font="Verdana"]Thanks
Chandra Mohan[/font]

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 11

Chand00 (7/25/2014)
Hi,
Thank you. Below one worked for me.
$QueryResults[0].Table.Columns.Count
Apologies. I tried to post but I was unable to post somehow. Once again Thankyou

Still, why does this need to be done from PowerShell? What is the overall task that you're trying to accomplish?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 12

Jeff Moden (7/25/2014)
Chand00 (7/25/2014)
Hi,
Thank you. Below one worked for me.
$QueryResults[0].Table.Columns.Count
Apologies. I tried to post but I was unable to post somehow. Once again Thankyou
Still, why does this need to be done from PowerShell? What is the overall task that you're trying to accomplish?

Just to supply the T-SQL answer to the thread, the stored procedure sp_describe_first_result_set does the job.

😎

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 13

Eirikur Eiriksson (7/25/2014)
Jeff Moden (7/25/2014)
Chand00 (7/25/2014)
Hi,
Thank you. Below one worked for me.
$QueryResults[0].Table.Columns.Count
Apologies. I tried to post but I was unable to post somehow. Once again Thankyou
Still, why does this need to be done from PowerShell? What is the overall task that you're trying to accomplish?
Just to supply the T-SQL answer to the thread, the stored procedure sp_describe_first_result_set does the job.
😎

So does a trip to sys.all_columns or sys.columns or INFORMATION_SCHEMA.columns (to name a couple of methods).

I still want to know why someone needs to do this from PowerShell. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 14

Jeff Moden (7/25/2014)
Eirikur Eiriksson (7/25/2014)
Jeff Moden (7/25/2014)
Chand00 (7/25/2014)
Hi,
Thank you. Below one worked for me.
$QueryResults[0].Table.Columns.Count
Apologies. I tried to post but I was unable to post somehow. Once again Thankyou
Still, why does this need to be done from PowerShell? What is the overall task that you're trying to accomplish?
Just to supply the T-SQL answer to the thread, the stored procedure sp_describe_first_result_set does the job.
😎
So does a trip to sys.all_columns or sys.columns or INFORMATION_SCHEMA.columns (to name a couple of methods).
I still want to know why someone needs to do this from PowerShell. 😉

I think this could possibly be a case of choosing the tool before the task but then again how could I know:-)

The procedure sp_describe_first_result_set does more than the meta data queries, if I remember correctly, it uses openrowset with the system only TABLE directive to retrieve the structure of the query results. It was introduced in SQL Server 2012 where sp_describe_first_result_set, sp_describe_undeclared_parameters, sys.dm_exec_describe_first_result_set, and sys.dm_exec_describe_first_result_set_for_object replace the SET FMTONLY directive.

😎