Emulate COMMENT ON in SQL Server using sp_updateextendedproperty

[lukaseder]

This is how it can be done:

EXEC sp_updateextendedproperty  --or sp_addextendedproperty 
@name = N'MS_Description', @value = 'Your description',
@level0type = N'Schema', @level0name = dbo, 
@level1type = N'Table',  @level1name = Your Table Name, 
@level2type = N'Column', @level2name = Yuur Column Name;

Or, more specifically (as we need to distinguish between "add" and "update"):

BEGIN TRY
  EXEC sp_addextendedproperty 'MS_Description', 'Table X', 'schema', 'dbo', 'table', 't';
END TRY
BEGIN CATCH
  EXEC sp_updateextendedproperty 'MS_Description', 'Table X', 'schema', 'dbo', 'table', 't';
END CATCH

Also, if a table is not qualified, then we can replace the 'schema', 'dbo' arguments by 'schema', 'schema_name()'

---

See:

• https://stackoverflow.com/a/9018619/521799
• https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-addextendedproperty-transact-sql