Skip to Content

How to modify the fields comments generation code targeting SQL Server 2012

Hi,

The code that is generated to add a comment for a SQL Server field SomeSchema.SomeTable.SomeField is this:

if exists(select 1 from sys.extended_properties p where
      p.major_id = object_id('SomeSchema.SomeTable')
  and p.minor_id = (select c.column_id from sys.columns c where c.object_id = p.major_id and c.name = 'SomeField')
)
begin
   execute sp_dropextendedproperty 'MS_Description', 
   'user', 'SomeSchema', 'table', 'SomeTable', 'column', 'SomeField'
end
execute sp_addextendedproperty 'MS_Description', 
   'blah blah',
   'user', 'SomeSchema', 'table', 'SomeTable', 'column', 'SomeField'
go

The problem is that using 'user' gives me the following error:

Lookup Error - SQL Server Database Error: Object is invalid. Extended properties are not permitted on 'SomeSchema.SomeTable.SomeField', or the object does not exist.

If SomeSchema is dbo then it works.

Is there a way to tweak the generation script to generate 'SCHEMA' instead of 'USER'. In their documentation, https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-addextendedproperty-transact-sql?view=sql-server-2017&viewFallbackFrom=sql-server-2012, they state:

The ability to specify USER as a level 0 type in an extended property of a level 1 type object will be removed in a future version of SQL Server. Use SCHEMA as the level 0 type instead.

Thanks

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

1 Answer

  • May 10 at 05:54 PM

    Ok, I think I found it, it is under, Database, Edit Current DBMS.., MSSQLSRV2012::Script\Objects\Column\ColumnComment:

    [if exists(select 1 from sys.extended_properties p where
          p.major_id = object_id('[%QUALIFIER%]%TABLE%')
      and p.minor_id = (select c.column_id from sys.columns c where c.object_id = p.major_id and c.name = %.q:COLUMN%)
    )
    begin
       [%OWNER%?[.O:[execute ][exec ]]sp_dropextendedproperty [%R%?[N]]'MS_Description', 
       [%R%?[N]]'user', [%R%?[N]]%.q:OWNER%, [%R%?[N]]'table', [%R%?[N]]%.q:TABLE%, [%R%?[N]]'column', [%R%?[N]]%.q:COLUMN%
    :declare @CurrentUser sysname
    select @CurrentUser = user_name()
    [.O:[execute ][exec ]]sp_dropextendedproperty [%R%?[N]]'MS_Description', 
       [%R%?[N]]'user', [%R%?[N]]@CurrentUser, [%R%?[N]]'table', [%R%?[N]]%.q:TABLE%, [%R%?[N]]'column', [%R%?[N]]%.q:COLUMN%
    ]
    end
    ][%OWNER%?[.O:[execute ][exec ]]sp_addextendedproperty [%R%?[N]]'MS_Description', 
       [%R%?[N]]%.q:COMMENT%,
       [%R%?[N]]'user', [%R%?[N]]%.q:OWNER%, [%R%?[N]]'table', [%R%?[N]]%.q:TABLE%, [%R%?[N]]'column', [%R%?[N]]%.q:COLUMN%
    :select @CurrentUser = user_name()
    [.O:[execute ][exec ]]sp_addextendedproperty [%R%?[N]]'MS_Description', 
       [%R%?[N]]%.q:COMMENT%,
       [%R%?[N]]'user', [%R%?[N]]@CurrentUser, [%R%?[N]]'table', [%R%?[N]]%.q:TABLE%, [%R%?[N]]'column', [%R%?[N]]%.q:COLUMN%
    ]

    Just curious, what is [%R%?[N]]?

    Add comment
    10|10000 characters needed characters exceeded