Getting query text from SSMS into Excel with PowerShell

  • Comments posted to this topic are about the item Getting query text from SSMS into Excel with PowerShell

    Shawn Melton
    Twitter: @wsmelton
    Blog: wsmelton.github.com
    Github: wsmelton

  • The title of this is misleading isn't it? Because you're not really getting query results out of SSMS, you're just re-running the query in PS. If you've got a resultset in SSMS that you can't reproduce, say there are blocks you want to preserve that you need to look into later, they may not be there when you run the query again, then this method won't work because you've actually got the results in SSMS and they'll be gone when you query them from PS.

    So while this is a good technique for putting results into excel and getting around the multiline problem, it doesn't exactly do what the title of the article says.

    Still, nice technique.

    Watch my free SQL Server Tutorials at:
    http://MidnightDBA.com
    Blog Author of:
    DBA Rant – http://www.MidnightDBA.com/DBARant

    Minion Maintenance is FREE:

  • KenpoDBA (9/7/2015)


    The title of this is misleading isn't it? Because you're not really getting query results out of SSMS, you're just re-running the query in PS. If you've got a resultset in SSMS that you can't reproduce, say there are blocks you want to preserve that you need to look into later, they may not be there when you run the query again, then this method won't work because you've actually got the results in SSMS and they'll be gone when you query them from PS.

    So while this is a good technique for putting results into excel and getting around the multiline problem, it doesn't exactly do what the title of the article says.

    Still, nice technique.

    Hmmm, nice evaluation.

  • But I want to make it clear that the article is solid. It just needs a better title.

    Watch my free SQL Server Tutorials at:
    http://MidnightDBA.com
    Blog Author of:
    DBA Rant – http://www.MidnightDBA.com/DBARant

    Minion Maintenance is FREE:

  • Actually, the title is misleading in another perspective, it is not "... into Excel..", it is simply into a CSV file.

    If it is indeed an Excel file, I'd say you should be able to export to different Excel sheets. 😛

  • You could use the Open Source DLL EPPlus to write the output from a SQL query directly to an Excel spreadsheet, e.g.,

    $cmdText = "select object_name(i.object_id) as [Table Name], i.name as [Index Name], case when i.type = 1 then 'Clustered' else 'Nonclustered' end as [Index Type], i.fill_factor as [Fill Factor], s.avg_fragmentation_in_percent as Fragmentation, s.page_count as [Page Count]

    from sys.dm_db_index_physical_stats(db_id(), null, null, null, null) s

    inner join sys.indexes i with (nolock) on i.object_id = s.object_id and i.index_id = s.index_id

    where i.name is not null

    and s.avg_fragmentation_in_percent > 5

    and s.page_count > 1000

    order by s.avg_fragmentation_in_percent desc"

    $dt = New-Object System.Data.DataTable

    $da = New-Object System.Data.SqlClient.SqlDataAdapter $cmdText, "Server=???;Database=???;Trusted_Connection=Yes"

    $da.SelectCommand.CommandTimeout = 300

    [void]$da.Fill($dt)

    $file = New-Object System.IO.FileInfo ("Book1.xlsx")

    if (Test-Path $file.FullName) {Remove-Item $file.FullName}

    Import-Module ($env:USERPROFILE + "\Documents\WindowsPowerShell\EPPlus.dll")

    $package = New-Object OfficeOpenXml.ExcelPackage $file

    $sheet = $package.Workbook.Worksheets.Add("Sheet1")

    [void]$sheet.Cells["A1"].LoadFromDataTable($dt, $true)

    $sheet.Row(1).Style.Font.Bold = $true

    $sheet.View.FreezePanes(2, 1)

    $sheet.Cells.AutoFitColumns()

    $sheet.Column(4).Style.NumberFormat.Format = "#,##0"

    $sheet.Column(5).Style.NumberFormat.Format = "##0.00"

    $sheet.Column(6).Style.NumberFormat.Format = "#,##0"

    [void]$package.Save()

    $package.Dispose()

    [void][System.Diagnostics.Process]::Start($file.FullName)

  • The PowerShell script in the article uses the SQLPS PowerShell module which may not exist on the computer on which you run the script. Modern versions of SQL Server or SSMS will install a SqlServer module instead; and this may not be immediately available for use in a script. It may be necessary (or expedient, at least) to add the following line to the script:

    Import-Module SqlServer

  • In SSMS Query Options you can untick "Retain CR/LF on copy or save" and then the copy will automatically remove the line feeds for you.

  • dave.rogers 954 - Friday, March 23, 2018 3:26 AM

    The PowerShell script in the article uses the SQLPS PowerShell module which may not exist on the computer on which you run the script. Modern versions of SQL Server or SSMS will install a SqlServer module instead; and this may not be immediately available for use in a script. It may be necessary (or expedient, at least) to add the following line to the script:

    Import-Module SqlServer

    That is incorrect. The sqlserver module is only available via the PowerShell Gallery. If you install SSMS 17.x you will get the sqlps module but none of the media for SQL Server will install the sqlserver module unless your run Install-Module.

    Shawn Melton
    Twitter: @wsmelton
    Blog: wsmelton.github.com
    Github: wsmelton

  • Shawn Melton - Friday, March 23, 2018 7:42 AM

    dave.rogers 954 - Friday, March 23, 2018 3:26 AM

    The PowerShell script in the article uses the SQLPS PowerShell module which may not exist on the computer on which you run the script. Modern versions of SQL Server or SSMS will install a SqlServer module instead; and this may not be immediately available for use in a script. It may be necessary (or expedient, at least) to add the following line to the script:

    Import-Module SqlServer

    That is incorrect. The sqlserver module is only available via the PowerShell Gallery. If you install SSMS 17.x you will get the sqlps module but none of the media for SQL Server will install the sqlserver module unless your run Install-Module.

    I believe it depends on versions of SSMS in terms of which module is or isn't installed. SQLServer module used to be included with 16.x versions of SSMS.
    SQL Server PowerShell

    They discontinued shipping Powershell with SSMS. I can only find the docs listing this as happening with 17.6 but I think it started with 17.4. The documentation states:
    The SQL Server PowerShell module is now a separate install through the PowerShell Gallery
    Download SQL Server Management Studio (SSMS)

    The screenshots and error messages when trying to use Powershell with 17.4 are in this article:
    SSMS Version 17.4 no more SQLPS Module

    Sue

  • I wish people would publish the powershell scripts as a list of statements rather than these compound one liners.

    412-977-3526 call/text

  • Thanks for the script.

    Right click inside the query results windows and choose "Save Results As..." will also preserve the formatting when a csv is opened in Excel.

Viewing 12 posts - 1 through 11 (of 11 total)

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