SQLServer批量添加字段注释

如题，先定义表类型：

CREATE TYPE column_comments AS TABLE(column_name VARCHAR(200),comment VARCHAR(1000))

CREATE PROC sp_add_comments @tmp_column_comments column_comments READONLY,@tb_name VARCHAR(200) AS
DECLARE @column_name VARCHAR(200),@comment VARCHAR(1000)
DECLARE c_cms CURSOR LOCAL FOR SELECT * FROM @tmp_column_comments
OPEN c_cms
FETCH NEXT FROM c_cms INTO @column_name,@comment
WHILE @@FETCH_STATUS = 0
BEGIN
    IF (SELECT COUNT(1) FROM fn_listextendedproperty('MS_Description','SCHEMA','dbo','TABLE',@tb_name,'COLUMN',@column_name)) > 0
        EXEC sp_updateextendedproperty @name = 'MS_Description',@value = @comment,@level0type = 'SCHEMA',@level0name = 'dbo',@level1type = 'TABLE',@level1name = @tb_name,@level2type = 'COLUMN',@level2name = @column_name
    ELSE
        EXEC sp_addextendedproperty @name = 'MS_Description',@level2name = @column_name
    FETCH NEXT FROM c_cms INTO @column_name,@comment
END
CLOSE c_cms
DEALLOCATE c_cms

DECLARE @tmp_cms column_comments
INSERT INTO @tmp_cms VALUES('id','主键ID')
EXEC sp_add_comments @tmp_cms,'table_test'

EOF.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!