2

I have a table named Assignment in my Azure SQL Server (version 12.0.1400).

The table itself, as well as most of the columns, have Description filled in. I've entered those Description values using the Table Designer in SQL Server Management Studio (SSMS version 18.1 15.0.18131.0).

My problem is that, when I script the table, I only see the Description of the table, but I can't see the Descriptions of columns, which are clearly visible in Object Explorer.

If you look at the screenshot below, you'll see that the table Assignment, as well as a column, AssignmentId, have Descriptions:

enter image description here

And when I script the table, the resulting SQL code is:

/****** Object:  Table [dbo].[Assignment]    Script Date: 09-Jul-19 13:52:54 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Assignment](
    [AssignmentId] [varchar](255) NOT NULL,
    [HITId] [varchar](255) NOT NULL,
    [WorkerId] [varchar](255) NOT NULL,
    [HITStatus] [varchar](50) NOT NULL,
    [HITTitle] [varchar](1024) NULL,
    [HITTypeId] [varchar](1024) NULL,
    [HITGroupId] [varchar](1024) NULL,
    [HITCreationTime] [datetimeoffset](7) NULL,
    [Deadline] [datetimeoffset](7) NULL,
    [AcceptTime] [datetimeoffset](7) NULL,
    [SubmitTime] [datetimeoffset](7) NULL,
    [ApprovalTime] [datetimeoffset](7) NULL,
    [RejectionTime] [datetimeoffset](7) NULL,
    [AssignmentStatus] [varchar](10) NULL,
    [ImageUrl] [varchar](1024) NULL,
    [AnswerFreeText] [text] NULL,
    [SimpleCheckFinishedTime] [datetime2](7) NULL,
    [SimpleCheckStatus] [bit] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[Assignment]  WITH CHECK ADD  CONSTRAINT [CK_Assignment_AssignmentStatus_Enumeration_Constraint] CHECK  (([AssignmentStatus]='Rejected' OR [AssignmentStatus]='Approved' OR [AssignmentStatus]='Submitted'))
GO

ALTER TABLE [dbo].[Assignment] CHECK CONSTRAINT [CK_Assignment_AssignmentStatus_Enumeration_Constraint]
GO

ALTER TABLE [dbo].[Assignment]  WITH CHECK ADD  CONSTRAINT [CK_Assignment_HITStatus_Enumeration_Constraint] CHECK  (([HITStatus]='Disposed' OR [HITStatus]='Reviewing' OR [HITStatus]='Reviewable' OR [HITStatus]='Unassignable' OR [HITStatus]='Assignable'))
GO

ALTER TABLE [dbo].[Assignment] CHECK CONSTRAINT [CK_Assignment_HITStatus_Enumeration_Constraint]
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The Assignment data structure represents a single assignment of a HIT to a Worker. The assignment tracks the Worker''s efforts to complete the HIT, and contains the results for later retrieval. ' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Assignment'
GO

The only Description in the resulting SQL script is the one for the table itself, and not for the columns.

I've also checked the options of Object Explorer:

enter image description here

But I still can't get those Description values for columns in the resulting SQL script.

How can I can extract them using Script Table in SQL Server Management Studio?

Emre Sevinç
  • 8,211
  • 14
  • 64
  • 105
  • FWIW SSMS version 18.0 on SQL Server scripts these description properties with no further options set. You could try and see if "generate script for dependent objects" makes a difference. – Jeroen Mostert Jul 09 '19 at 12:19
  • @JeroenMostert I wrote my SSMS version wrong (it's version 18.1, but Copy Info from "About" screen displays the component version as I reported). Anyway, I've also set "Generate script for dependent objects" to True, and I ran "Script Table as" again. Unfortunately, still no Description values for the columns themselves :( – Emre Sevinç Jul 09 '19 at 12:30
  • The Description values are obviously there, because I can already see them when I run the following: `SELECT objtype, objname, name, value FROM fn_listextendedproperty (NULL, 'schema', 'dbo', 'table', 'Assignment', 'column', default);` , but I can't extract them into a script via SMSS even if I script entire database and all objects – Emre Sevinç Jul 09 '19 at 12:40
  • When I update the Description field on a column in my test table, it creates an extended property called "MS_Description" with that value. Then when I do a right click>Script Table, I get an extended property at the end of my script. Are you not getting the extended property with your script? Like so: `EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'TEST' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ComputedColumns', @level2type=N'COLUMN',@level2name=N'Comp1' GO` Might just be an Azure problem specifically? – Jacob H Jul 09 '19 at 12:42
  • 1
    Then that might be an incompatibility in Azure, possibly in combination with SSMS itself. You can [submit an issue](http://aka.ms/sqlfeedback). Working around it "manually" is simple enough (you can just manually generate `sp_addextendedproperty` calls with the output you get from `fn_listextendedproperty`). – Jeroen Mostert Jul 09 '19 at 12:42
  • @JeroenMostert and @JacobH, I think you're right suggesting a bug, because I've tried something similar on an *on-premise*, *non-Azure* MS SQL Server 2016, and Script Table returned `EXEC sys.sp_addextendedproperty @name=N'MS_Description' ...` lines for all the Columns whose `Description`s I've filled in the Table Designer. – Emre Sevinç Jul 09 '19 at 13:30

0 Answers0