--添加或者更新字段说明
ALTER PROC [dbo].[AddOrUpdateColnumComment]
(
@tableName sysname ,
@colnumName sysname ,
@comment NVARCHAR(50)
)
AS ;
IF EXISTS ( SELECT *
FROM dbo.syscolumns a
LEFT JOIN dbo.systypes b ON a.xusertype = b.xusertype
INNER JOIN dbo.sysobjects d ON a.id = d.id
AND d.xtype = 'U'
AND d.name <> 'dtproperties'
LEFT JOIN dbo.syscomments e ON a.cdefault = e.id
LEFT JOIN sys.extended_properties g ON a.id = g.major_id
AND a.colid = g.minor_id
LEFT JOIN sys.extended_properties f ON d.id = f.major_id
AND f.minor_id = 0
WHERE d.name = @tableName
AND a.name = @colnumName
AND g.value IS NOT NULL )
BEGIN
EXECUTE sys.sp_updateextendedproperty N'MS_Description', @comment,
N'SCHEMA', N'dbo', N'TABLE', @tableName, N'COLUMN',
@colnumName;
END;
ELSE
BEGIN
EXEC sp_addextendedproperty 'MS_Description', @comment, 'user',
dbo, 'table', @tableName, 'column', @colnumName;
END;
