DROP PROC sp_support_set_sp_description
go
CREATE PROC sp_support_set_sp_description(@sp_name VARCHAR(500),@sp_description VARCHAR(1000))
AS
BEGIN
SET NOCOUNT ON
DECLARE @sp_id INT=0,@sp_description_id INT=0
SELECT top 1 @sp_id=A.object_id,@sp_description_id=D.major_id
FROM sys.procedures A
LEFT JOIN sys.extended_properties D ON D.major_id = A.object_id AND D.minor_id = 0 and D.name='SP_MS_Description'
WHERE A.name = @sp_name
SELECT @sp_id=ISNULL(@sp_id,0),@sp_description_id =ISNULL(@sp_description_id,0)
IF @sp_id>0
BEGIN
IF @sp_description_id=0
BEGIN
exec sys.sp_addextendedproperty @name = N'SP_MS_Description',@value =@sp_description,@level0type = N'schema',@level0Name =N'dbo',@level1type = N'procedure',@level1name = @sp_name
END
ELSE
BEGIN
exec sys.sp_updateextendedproperty @name = N'SP_MS_Description',@value =@sp_description,@level0type = N'schema',@level0Name =N'dbo',@level1type = N'procedure',@level1name = @sp_name
END
END
END
--sp_dropextendproperty 为删除某个扩展属性