Question 105002 in Azure Question posted in
The official documentation to get you started can be found here.

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

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
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    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

  2. 0

    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
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search