Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

How to find number of columns returned from query in powershell Expand / Collapse
Author
Message
Posted Friday, July 25, 2014 2:21 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Sunday, December 14, 2014 6:16 AM
Points: 531, Visits: 1,141
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.


Thanks
Chandra Mohan
Post #1596116
Posted Friday, July 25, 2014 4:20 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, December 23, 2014 5:52 PM
Points: 5,831, Visits: 3,751
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!!!
Post #1596149
Posted Friday, July 25, 2014 5:02 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Sunday, December 14, 2014 6:16 AM
Points: 531, Visits: 1,141
I tried it already does not work

$QueryResults.Columns.Count
or
$QueryResults.Items.Count

is not working


Thanks
Chandra Mohan
Post #1596155
Posted Friday, July 25, 2014 7:53 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, December 23, 2014 5:52 PM
Points: 5,831, Visits: 3,751
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!!!
Post #1596220
Posted Friday, July 25, 2014 8:02 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Sunday, December 14, 2014 6:16 AM
Points: 531, Visits: 1,141
System.Object[]


Thanks
Chandra Mohan
Post #1596224
Posted Friday, July 25, 2014 8:07 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, December 23, 2014 5:52 PM
Points: 5,831, Visits: 3,751
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!!!
Post #1596228
Posted Friday, July 25, 2014 8:13 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Sunday, December 14, 2014 6:16 AM
Points: 531, Visits: 1,141
It gives only the number of rows but what I want is number of columns

Thanks
Chandra Mohan
Post #1596233
Posted Friday, July 25, 2014 8:49 AM This worked for the OP Answer marked as solution


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, December 23, 2014 5:52 PM
Points: 5,831, Visits: 3,751
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!!!
Post #1596256
Posted Friday, July 25, 2014 9:59 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, December 23, 2014 5:52 PM
Points: 5,831, Visits: 3,751
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!!!
Post #1596302
Posted Friday, July 25, 2014 11:16 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 4:23 PM
Points: 35,821, Visits: 32,494
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??


--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1596329
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse