[Bug] all descriptions are truncated to 254 characters

[aisbergde]

Describe the bug
I tried to document a database containing descriptions for schemas, objects and columns in extended properties ms_description. in the final documentation all descriptions are truncated to a length of 254 characters.

To Reproduce
create long descriptions in objects and in columns

CREATE TABLE [config].[Parameter](
	[Parameter_name] [varchar](100) NOT NULL,
	[sub_Parameter] [nvarchar](128) NOT NULL,
	[Parameter_desciption] [nvarchar](1000) NULL,
	[Parameter_default_value] [sql_variant] NULL,
	[Parameter_value] [sql_variant] NULL,
	[Parameter_value__result_nvarchar]  AS (TRY_CAST(coalesce([Parameter_value],[Parameter_default_value]) AS [nvarchar](4000))),
	[Parameter_value__result_int]  AS (case when NOT sql_variant_property([Parameter_default_value],'BaseType')='uniqueidentifier' AND NOT sql_variant_property([Parameter_value],'BaseType')='uniqueidentifier' then TRY_CAST(coalesce([Parameter_value],[Parameter_default_value]) AS [int])  end),
	[Parameter_value__result_date]  AS (case when NOT [Parameter_value] IS NULL AND NOT sql_variant_property([Parameter_value],'BaseType')='uniqueidentifier' then TRY_CAST([Parameter_value] AS [date]) when NOT [Parameter_default_value] IS NULL AND NOT sql_variant_property([Parameter_default_value],'BaseType')='uniqueidentifier' then CONVERT([date],TRY_CAST([Parameter_default_value] AS [datetime]))  end),
	[Parameter_value__result_datetime]  AS (case when NOT [Parameter_value] IS NULL AND NOT sql_variant_property([Parameter_value],'BaseType')='uniqueidentifier' then TRY_CAST([Parameter_value] AS [datetime]) when NOT [Parameter_default_value] IS NULL AND NOT sql_variant_property([Parameter_default_value],'BaseType')='uniqueidentifier' then TRY_CAST([Parameter_default_value] AS [datetime])  end),
 CONSTRAINT [PK_Parameter] PRIMARY KEY CLUSTERED 
(
	[Parameter_name] ASC,
	[sub_Parameter] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [config].[Parameter] ADD  CONSTRAINT [DF__Parameter__sub_P__18B6AB08]  DEFAULT ('') FOR [sub_Parameter]
GO

EXEC sys.sp_addextendedproperty @name=N'RepoObjectColumn_guid', @value=N'11f37926-9d61-eb11-84dc-a81e8446d5b0' , @level0type=N'SCHEMA',@level0name=N'config', @level1type=N'TABLE',@level1name=N'Parameter', @level2type=N'COLUMN',@level2name=N'Parameter_name'
GO

EXEC sys.sp_addextendedproperty @name=N'RepoObjectColumn_guid', @value=N'12f37926-9d61-eb11-84dc-a81e8446d5b0' , @level0type=N'SCHEMA',@level0name=N'config', @level1type=N'TABLE',@level1name=N'Parameter', @level2type=N'COLUMN',@level2name=N'sub_Parameter'
GO

EXEC sys.sp_addextendedproperty @name=N'RepoObject_guid', @value=N'69cbc49c-3862-eb11-84dc-a81e8446d5b0' , @level0type=N'SCHEMA',@level0name=N'config', @level1type=N'TABLE',@level1name=N'Parameter', @level2type=N'CONSTRAINT',@level2name=N'DF__Parameter__sub_P__18B6AB08'
GO

EXEC sys.sp_addextendedproperty @name=N'RepoObjectColumn_guid', @value=N'13f37926-9d61-eb11-84dc-a81e8446d5b0' , @level0type=N'SCHEMA',@level0name=N'config', @level1type=N'TABLE',@level1name=N'Parameter', @level2type=N'COLUMN',@level2name=N'Parameter_desciption'
GO

EXEC sys.sp_addextendedproperty @name=N'RepoObjectColumn_guid', @value=N'14f37926-9d61-eb11-84dc-a81e8446d5b0' , @level0type=N'SCHEMA',@level0name=N'config', @level1type=N'TABLE',@level1name=N'Parameter', @level2type=N'COLUMN',@level2name=N'Parameter_default_value'
GO

EXEC sys.sp_addextendedproperty @name=N'RepoObjectColumn_guid', @value=N'15f37926-9d61-eb11-84dc-a81e8446d5b0' , @level0type=N'SCHEMA',@level0name=N'config', @level1type=N'TABLE',@level1name=N'Parameter', @level2type=N'COLUMN',@level2name=N'Parameter_value'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'(TRY_CAST(coalesce([Parameter_value],[Parameter_default_value]) AS [nvarchar](4000)))' , @level0type=N'SCHEMA',@level0name=N'config', @level1type=N'TABLE',@level1name=N'Parameter', @level2type=N'COLUMN',@level2name=N'Parameter_value__result_nvarchar'
GO

EXEC sys.sp_addextendedproperty @name=N'ReferencedObjectColumnList', @value=N'[repo].[Parameter].[Parameter_default_value]
[repo].[Parameter].[Parameter_value]' , @level0type=N'SCHEMA',@level0name=N'config', @level1type=N'TABLE',@level1name=N'Parameter', @level2type=N'COLUMN',@level2name=N'Parameter_value__result_nvarchar'
GO

EXEC sys.sp_addextendedproperty @name=N'RepoObjectColumn_guid', @value=N'16f37926-9d61-eb11-84dc-a81e8446d5b0' , @level0type=N'SCHEMA',@level0name=N'config', @level1type=N'TABLE',@level1name=N'Parameter', @level2type=N'COLUMN',@level2name=N'Parameter_value__result_nvarchar'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'(case when NOT sql_variant_property([Parameter_default_value],''BaseType'')=''uniqueidentifier'' AND NOT sql_variant_property([Parameter_value],''BaseType'')=''uniqueidentifier'' then TRY_CAST(coalesce([Parameter_value],[Parameter_default_value]) AS [int])  end)' , @level0type=N'SCHEMA',@level0name=N'config', @level1type=N'TABLE',@level1name=N'Parameter', @level2type=N'COLUMN',@level2name=N'Parameter_value__result_int'
GO

EXEC sys.sp_addextendedproperty @name=N'RepoObjectColumn_guid', @value=N'75b33a4a-426d-eb11-84e2-a81e8446d5b0' , @level0type=N'SCHEMA',@level0name=N'config', @level1type=N'TABLE',@level1name=N'Parameter', @level2type=N'COLUMN',@level2name=N'Parameter_value__result_int'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'(case when NOT [Parameter_value] IS NULL AND NOT sql_variant_property([Parameter_value],''BaseType'')=''uniqueidentifier'' then TRY_CAST([Parameter_value] AS [date]) when NOT [Parameter_default_value] IS NULL AND NOT sql_variant_property([Parameter_default_value],''BaseType'')=''uniqueidentifier'' then CONVERT([date],TRY_CAST([Parameter_default_value] AS [datetime]))  end)' , @level0type=N'SCHEMA',@level0name=N'config', @level1type=N'TABLE',@level1name=N'Parameter', @level2type=N'COLUMN',@level2name=N'Parameter_value__result_date'
GO

EXEC sys.sp_addextendedproperty @name=N'RepoObjectColumn_guid', @value=N'a87796eb-adc2-eb11-b344-00262d0c399b' , @level0type=N'SCHEMA',@level0name=N'config', @level1type=N'TABLE',@level1name=N'Parameter', @level2type=N'COLUMN',@level2name=N'Parameter_value__result_date'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'(case when NOT [Parameter_value] IS NULL AND NOT sql_variant_property([Parameter_value],''BaseType'')=''uniqueidentifier'' then TRY_CAST([Parameter_value] AS [datetime]) when NOT [Parameter_default_value] IS NULL AND NOT sql_variant_property([Parameter_default_value],''BaseType'')=''uniqueidentifier'' then TRY_CAST([Parameter_default_value] AS [datetime])  end)' , @level0type=N'SCHEMA',@level0name=N'config', @level1type=N'TABLE',@level1name=N'Parameter', @level2type=N'COLUMN',@level2name=N'Parameter_value__result_datetime'
GO

EXEC sys.sp_addextendedproperty @name=N'RepoObjectColumn_guid', @value=N'a97796eb-adc2-eb11-b344-00262d0c399b' , @level0type=N'SCHEMA',@level0name=N'config', @level1type=N'TABLE',@level1name=N'Parameter', @level2type=N'COLUMN',@level2name=N'Parameter_value__result_datetime'
GO

EXEC sys.sp_addextendedproperty @name=N'RepoObject_guid', @value=N'2690291c-9d61-eb11-84dc-a81e8446d5b0' , @level0type=N'SCHEMA',@level0name=N'config', @level1type=N'TABLE',@level1name=N'Parameter', @level2type=N'CONSTRAINT',@level2name=N'PK_Parameter'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'allgemeine Parameter der Datenbank, beispielsweise für ETL Prozesse. +
Standard-Werte werden in der Sicht `[config].[Parameter_default]` fest kodiert und in die Spalte `Parameter_default_value` übernommen. +
Die Standard-Werte können in der optionalen Spalte `Parameter_value` überschrieben werden. +
Die Spalten `Parameter_value__result_...` liefern den angewendeten Parameter, konvertiert in verschiedene Datentypen.' , @level0type=N'SCHEMA',@level0name=N'config', @level1type=N'TABLE',@level1name=N'Parameter'
GO

EXEC sys.sp_addextendedproperty @name=N'RepoObject_guid', @value=N'2490291c-9d61-eb11-84dc-a81e8446d5b0' , @level0type=N'SCHEMA',@level0name=N'config', @level1type=N'TABLE',@level1name=N'Parameter'
GO

write the output into a file:

sqlcmd -S myserver -d mydatatabase -Q "EXEC dbo.sp_doc @DatabaseName = 'mydatatabase' , @ExtendedPropertyName = 'ms_description'" -o "mypath\Dokumentation\mydatatabase.md" -f 65001 

check the result

Expected behavior
descriptions should not be truncated

Versions(please complete the following information):

• OS: windows 10
• SQL Server: 2019
• SSMS: not uses, but sqlcmd
• sp_doc Version: 20210412

Additional context

[lowlydba]

Thanks @aisbergde for bringing this up. The fix should be to specify -y 0 to sqlcmd when running it, otherwise it defaults to 256 char as the max length for variable length output.

In #208 I updated the documentation to make it more clear that this is an important option to specify.