util_set_comments.sql

-- usage 1: EXEC lb_util_comment_table N'stock_location', N'Stores history of GPS co-ordinates for stock items';
-- usage 2: EXEC lb_util_comment_column @TableName=N'stock_location', @ColumnName=N'measured_at', N'datestamp of location measured';

CREATE OR ALTER procedure lb_util_comment_column @TableName SYSNAME,
                                                 @ColumnName nvarchar(200),
                                                 @MS_DescriptionValue nvarchar(200)
as


declare @MS_Description nvarchar(200) = null;
declare @TableId int
declare @ColumnId int
declare @SchemaName SYSNAME

select @SchemaName = SCHEMA_NAME()

select @TableId = T.object_id
from sys.tables T,
     sys.schemas S
where S.schema_id = T.schema_id
  and T.name = @TableName
  AND S.name = @SchemaName;

SELECT @ColumnId = c.column_id
FROM sys.columns c
WHERE c.object_id = @TableId
  AND c.name = @ColumnName;

    SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(200)) AS [MS_Description]
                           FROM sys.extended_properties AS ep
                           WHERE ep.major_id = OBJECT_ID(@TableName)
                             AND ep.name = N'MS_Description'
                             AND ep.minor_id = @ColumnId);
    IF @MS_Description IS NULL
        BEGIN
            EXEC sys.sp_addextendedproperty
                 @name = N'MS_Description',
                 @value = @MS_DescriptionValue,
                 @level0type = N'Schema',
                 @level0name = @SchemaName,
                 @level1type = N'Table',
                 @level1name = @TableName,
                 @level2type = N'Column',
                 @level2name = @ColumnName;
        END
    ELSE
        BEGIN
            EXEC sys.sp_updateextendedproperty
                 @name = N'MS_Description',
                 @value = @MS_DescriptionValue,
                 @level0type = N'Schema',
                 @level0name = @SchemaName,
                 @level1type = N'Table',
                 @level1name = @TableName,
                 @level2type = N'Column',
                 @level2name = @ColumnName;
        END
    return @@Error
GO
-- End of routine
GRANT EXECUTE ON lb_util_comment_column TO [public]


CREATE OR ALTER procedure lb_util_comment_table @TableName SYSNAME,
                                                @MS_DescriptionValue nvarchar(200)
as


declare @MS_Description nvarchar(200) = null;
declare @TableId int
declare @SchemaName SYSNAME
SELECT @SchemaName = SCHEMA_NAME()

select @TableId = T.object_id
from sys.tables T,
     sys.schemas S
where S.schema_id = T.schema_id
  and T.name = @TableName
  AND S.name = @SchemaName;

    SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(200)) AS [MS_Description]
                           FROM sys.extended_properties AS ep
                           WHERE ep.major_id = OBJECT_ID(@TableName)
                             AND ep.name = N'MS_Description'
                             AND ep.minor_id = 0);
    IF @MS_Description IS NULL
        BEGIN
            EXEC sys.sp_addextendedproperty
                 @name = N'MS_Description',
                 @value = @MS_DescriptionValue,
                 @level0type = N'Schema',
                 @level0name = @SchemaName,
                 @level1type = N'Table',
                 @level1name = @TableName;
        END
    ELSE
        BEGIN
            EXEC sys.sp_updateextendedproperty
                 @name = N'MS_Description',
                 @value = @MS_DescriptionValue,
                 @level0type = N'Schema',
                 @level0name = @SchemaName,
                 @level1type = N'Table',
                 @level1name = @TableName;
        END
    return @@Error
GO
-- End of routine
GRANT EXECUTE ON lb_util_comment_table TO [public]