How to find number of columns returned from query in powershell

  • 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]

  • 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!!!

  • I tried it already does not work

    $QueryResults.Columns.Count

    or

    $QueryResults.Items.Count

    is not working

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

  • 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!!!

  • System.Object[]

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

  • 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!!!

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

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

  • 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!!!

  • 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!!!

  • 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)

  • 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]

  • 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)

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

    😎

  • 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)

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

    😎

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

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