BUG: sp_helpme: nvarchar columns are listed twice in the column-resultset

[ChrMaa]

/*
BUG: sp_helpme: nvarchar columns are listed twice in the column-resultset

Execute the code. you will see 3 rows in the column-resultset where only 2 rows should be

Microsoft SQL Server 2019 (RTM-CU10) (KB5001090) - 15.0.4123.1 (X64) Mar 22 2021 18:10:24
Copyright (C) 2019 Microsoft Corporation Developer Edition (64-bit) on Windows 10 Pro 10.0 (Build 19043: ) (Hypervisor)
*/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TestTable]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[TestTable](
[nv] nvarchar NULL,
[v] varchar NULL
) ON [PRIMARY]
END
GO

IF NOT EXISTS (SELECT * FROM sys.fn_listextendedproperty(N'Description' , N'SCHEMA',N'dbo', N'TABLE',N'TestTable', N'COLUMN',N'nv'))
EXEC sys.sp_addextendedproperty @name=N'Description', @value=N'Desc1' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'TestTable', @level2type=N'COLUMN',@level2name=N'nv'
GO

IF NOT EXISTS (SELECT * FROM sys.fn_listextendedproperty(N'Description' , N'SCHEMA',N'dbo', N'TABLE',N'TestTable', N'COLUMN',N'v'))
EXEC sys.sp_addextendedproperty @name=N'Description', @value=N'Desc2' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'TestTable', @level2type=N'COLUMN',@level2name=N'v'
GO

EXEC sp_helpme 'dbo.TestTable'

[lowlydba]

Thanks for the bug submission! Looks like an easy fix, will have a development branch ready for you to validate soon.

[ChrMaa]

the duplicated row ist eliminated. But the value of the Length-Column ist twice the real size of the column.
in my opinion the values in TrimTrailingBlanks and FixedLenNullInSource must be the same.
use sp_helpme 'dbo.TestTable' to get the new problems visible.
Thanks for your help

[lowlydba]

Glad its working!

But the value of the Length-Column ist twice the real size of the column.

If you are referring to it showing 2 as the Length for the NVARCHAR column in your example, that is because it is returning the length in bytes. For NVARCHAR, each character is 2 bytes. This is consistent with how the original sp_help works. The intention behind sp_helpme is to augment the data output, but not modify the original functionality.

in my opinion the values in TrimTrailingBlanks and FixedLenNullInSource must be the same.

Same as above, these two columns are exact copies of sp_help's logic. The documentation specifies FixedLenNullInSource is for backwards compatibility only, so I would not recommend using it.

If you encounter any more 🐛 , feel free to open new issues for them!