本文主要是介绍SQL添加表名注释字段注释存储过程[与查询关联]，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！
USE [DEV2]
GO/****** Object: StoredProcedure [dbo].[Update_table_or_field_info_sp] Script Date: 2018-3-30 22:57:14 ******/
SET ANSI_NULLS ON
GOSET QUOTED_IDENTIFIER ON
GO-- =============================================
-- Author: <骚团长·jway>
-- Create date: <2018-03-30>
-- Description: <sql server 添加表注释、字段注释>与存储过程[Get_table_or_field_info_sp]相互照应
-- =============================================
CREATE PROCEDURE [dbo].[Update_table_or_field_info_sp]
@Type VARCHAR(30)=NULL,--传入类型
@TabelName NVARCHAR(15)=NULL,--传入表名
@RemarkTabelName NVARCHAR(200),--传入表名注释
@FieldName NVARCHAR(15)=NULL,--传入字段名
@RemarkFieldName NVARCHAR(200)=NULL--传入字段名注释/*******************************************
* 存储过程调用方法：EXEC Update_table_or_field_info_sp@Type = 'AddFieldName',@TabelName = 'mfworkorder',@RemarkTabelName = '',@FieldName = 'factoryid',@RemarkFieldName = N'这是厂别哦~~'*******************************************/AS
BEGINSET NOCOUNT ON;/*******************************************对表名进行注释、修改注释、删除注释BEGIN*******************************************/IF @Type='AddTabelName'--增加表名注释BEGINEXECUTE sp_addextENDedproperty 'MS_Description',@RemarkTabelName,'user','dbo','table',@TabelName,NULL,NULL; SELECT N'增加表名('+@TabelName+N')的注释:'+@RemarkTabelName+N',成功!' AS RetValueRETURN 0ENDIF @Type='UpdateTabelName'--更新表名注释BEGINEXECUTE sp_updateextENDedproperty 'MS_Description',@RemarkTabelName,'user','dbo','table',@TabelName,NULL,NULL; SELECT N'更新表名('+@TabelName+N')的注释:'+@RemarkTabelName+N',成功!' AS RetValueRETURN 0 ENDIF @Type='DeleteTabelName'--删除表名注释BEGINEXECUTE sp_dropextENDedproperty 'MS_Description','user','dbo','table',@TabelName,NULL,NULL; SELECT N'删除表名('+@TabelName+N')的注释成功!' AS RetValueRETURN 0END/*******************************************对表名进行注释、修改注释、删除注释END*******************************************/ /*******************************************对表字段进行注释、修改注释、删除注释BEGIN*******************************************/ IF @Type='AddFieldName'--增加表字段注释BEGINEXECUTE sp_addextENDedproperty 'MS_Description',@RemarkFieldName,'user','dbo','table',@TabelName,'column',@FieldName; SELECT N'增加表('+@TabelName+N')字段('+@FieldName+N')的注释:'+@RemarkFieldName+N',成功!' AS RetValueRETURN 0ENDIF @Type='UpdateFieldName'--更新表字段BEGINEXECUTE sp_updateextENDedproperty 'MS_Description',@RemarkFieldName,'user','dbo','table',@TabelName,'column',@FieldName; SELECT N'更新表('+@TabelName+N')字段('+@FieldName+N')的注释:'+@RemarkFieldName+N',成功!' AS RetValueRETURN 0END IF @Type='DeleteFieldName'--删除表字段BEGINEXECUTE sp_dropextENDedproperty 'MS_Description','user','dbo','table',@TabelName,'column',@FieldName SELECT N'删除表('+@TabelName+N')字段('+@FieldName+N')的注释成功!' AS RetValue RETURN 0END/*******************************************对表字段进行注释、修改注释、删除注释END*******************************************/ SET NOCOUNT OFF
END
BEGINRAISERROR 99999N'Update_table_or_field_info_sp 存储过程传入参数错误 'RETURN 101
ENDGO这篇关于SQL添加表名注释字段注释存储过程[与查询关联]的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
