PowerShell 5.1 ISE Why aren't all my columns showing when using Format-Table

userOmA 0
Viewed 35

I have a table with 32 columns and when I try to output that with Format-Table in PowerShell it cuts off a good bit of the colunmns as shown in image below.

I included the script below that creates the table as well as records.

$sqlResult = Invoke-Sqlcmd -Query 'select * from Table_1' -ServerInstance '(localdb)\MSSQLLocalDB' -Database 'Database1' -OutputAs DataTables

$sqlResult | Format-Table

How do I output all the columns in table format in following:

  1. in PowerShell 5.1 ISE or any other console
  2. in Azure pipelines logging

enter image description here

USE [Database1]
GO
/****** Object:  Table [dbo].[Table_1]    Script Date: 9/14/2022 5:51:07 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Table_1](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Column1] [nvarchar](50) NULL,
    [Column2] [nvarchar](50) NULL,
    [Column3] [nvarchar](50) NULL,
    [Column4] [nvarchar](50) NULL,
    [Column5] [nvarchar](50) NULL,
    [Column6] [nvarchar](50) NULL,
    [Column7] [nvarchar](50) NULL,
    [Column8] [nvarchar](50) NULL,
    [Column9] [nvarchar](50) NULL,
    [Column10] [nvarchar](50) NULL,
    [Column11] [nvarchar](50) NULL,
    [Column12] [nvarchar](50) NULL,
    [Column13] [nvarchar](50) NULL,
    [Column14] [nvarchar](50) NULL,
    [Column15] [nvarchar](50) NULL,
    [Column16] [nvarchar](50) NULL,
    [Column17] [nvarchar](50) NULL,
    [Column18] [nvarchar](50) NULL,
    [Column19] [nvarchar](50) NULL,
    [Column20] [nvarchar](50) NULL,
    [Column21] [nvarchar](50) NULL,
    [Column22] [nvarchar](50) NULL,
    [Column23] [nvarchar](50) NULL,
    [Column24] [nvarchar](50) NULL,
    [Column25] [nvarchar](50) NULL,
    [Column26] [nvarchar](50) NULL,
    [Column27] [nvarchar](50) NULL,
    [Column28] [nvarchar](50) NULL,
    [Column29] [nvarchar](50) NULL,
    [Column30] [nvarchar](50) NULL,
    [Column31] [nvarchar](50) NULL,
    [Column32] [nvarchar](50) NULL,
 CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[Table_1] ON 
GO
INSERT [dbo].[Table_1] ([Id], [Column1], [Column2], [Column3], [Column4], [Column5], [Column6], [Column7], [Column8], [Column9], [Column10], [Column11], [Column12], [Column13], [Column14], [Column15], [Column16], [Column17], [Column18], [Column19], [Column20], [Column21], [Column22], [Column23], [Column24], [Column25], [Column26], [Column27], [Column28], [Column29], [Column30], [Column31], [Column32]) VALUES (1, N'12345678901234567890212345678903', N'12345678901234567890212345678903', N'12345678901234567890212345678903', N'12345678901234567890212345678903', N'12345678901234567890212345678903', N'12345678901234567890212345678903', N'12345678901234567890212345678903', N'12345678901234567890212345678903', N'12345678901234567890212345678903', N'12345678901234567890212345678903', N'12345678901234567890212345678903', N'12345678901234567890212345678903', N'12345678901234567890212345678903', N'12345678901234567890212345678903', N'12345678901234567890212345678903', N'12345678901234567890212345678903', N'12345678901234567890212345678903', N'12345678901234567890212345678903', N'12345678901234567890212345678903', N'12345678901234567890212345678903', N'12345678901234567890212345678903', N'12345678901234567890212345678903', N'12345678901234567890212345678903', N'12345678901234567890212345678903', N'12345678901234567890212345678903', N'12345678901234567890212345678903', N'12345678901234567890212345678903', N'12345678901234567890212345678903', N'12345678901234567890212345678903', N'12345678901234567890212345678903', N'12345678901234567890212345678903', N'12345678901234567890212345678903')
GO
SET IDENTITY_INSERT [dbo].[Table_1] OFF
GO
powershell azure
2 Answers

The short answer is "because your terminal isn't wide enough to display everything".

You can work around this behavior by piping to Out-String and providing a very large -Width:

... | Format-Table | Out-String -Width 10kb
Start-Automating0

This seems to work in PowerShell ISE and somewhat in console:

    function Main
    {
        
        $Host.UI.RawUI.BufferSize = New-Object Management.Automation.Host.Size (1800, 25)
            
        $sqlResult = Invoke-Sqlcmd -Query 'select * from Table_1' -ServerInstance '(localdb)\MSSQLLocalDB' -Database 'Database1' -OutputAs DataTables
        $columns = ($sqlResult.Columns -join ',') -split ','
        $sqlResult | Format-Table -Property $columns -AutoSize | Out-Host
    }  
    Main
userOmA0
Related