代码分享-SQLServer设置表或者列的说明

原创已于 2022-03-14 11:38:35 修改 · 1.9k 阅读

4 ·

CC 4.0 BY-SA版权

文章标签：

#sqlserver #数据库 #sql

于 2022-03-14 11:37:16 首次发布

SQLServer 专栏收录该内容

4 篇文章

订阅专栏

这篇博客介绍了如何在SQLServer中管理表和列的注释，提供了一个存储过程`AddOrUpdExtendedproperty`，该过程简化了新增和更新注释的操作，无需判断当前是新增还是更新。存储过程接受类型、模式、表名、列名和描述作为参数，能够对表和列添加或更新MS_Description的extended_property。

在SQLServer数据库中，是可以设置表或者列的注释说明的，但是还比较复杂，而且分成新增和修改两个不同的
存储过程，这就意味着你可能得切换，当表本身已经有说明的时候，
你就必须调用更新的存储过程，否则的话就用新增的。具体的例子如下：

标准方式使用如下：

	--新增时
	EXEC sys.sp_addextendedproperty @name = NULL, -- sysname
	    @value = NULL, -- sql_variant
	    @level0type = '', -- varchar(128)
	    @level0name = NULL, -- sysname
	    @level1type = '', -- varchar(128)
	    @level1name = NULL, -- sysname
	    @level2type = '', -- varchar(128)
	    @level2name = NULL -- sysname
	--修改时
	EXEC  sys.sp_updateextendedproperty @name = NULL, -- sysname
	    @value = NULL, -- sql_variant
	    @level0type = '', -- varchar(128)
	    @level0name = NULL, -- sysname
	    @level1type = '', -- varchar(128)
	    @level1name = NULL, -- sysname
	    @level2type = '', -- varchar(128)
	    @level2name = NULL -- sysname

这里提供一个存储过程，用于直接调用，无须考虑当前是新增还是更新。具体代码与参数说明如下：

--具体的参数以及说明如下，可用于对表,视图，以及列进行添加说明。 
ALTER PROC [dbo].[AddOrUpdExtendedproperty]
(
	@Type NVARCHAR(100), -- 'Table/Column/View'，视图或者表的时候填Table/Column都可以
	@Schema NVARCHAR(100), 
	@TableName NVARCHAR(100),--表名/视图名
	@ColumnName NVARCHAR(100), --列名
	@Description NVARCHAR(200) --说明
)
AS 
	
	DECLARE @Level1Type NVARCHAR(100) = (
		SELECT TOP 1
			[object].[type]  
			FROM sys.objects [object]
			INNER JOIN sys.schemas [schema] ON [object].[schema_id]= [schema].[schema_id]
			WHERE [schema].name + '.' + [object].name = @Schema + '.' + @TableName
	)
	IF @Level1Type  NOT IN ('U','V') 
	BEGIN
		RETURN;
	END 

	IF @Level1Type = N'V' 
	BEGIN
		SET @Level1Type = N'View'
	END
	ELSE
    BEGIN
		SET @Level1Type = 'Table'
	END 

	if object_id('tempdb..#tmp_DescDt') is not null    
		 drop table #tmp_DescDt

	SELECT  SchemaName,TableName,TableDesc,TableDescExists,ColName,ColDesc,ColDescExists INTO #tmp_DescDt FROM (

			SELECT i.name AS SchemaName,
				d.name AS TableName,
				isnull(f.value,'') AS TableDesc,
				CASE WHEN f.Value IS NULL OR f.Value = NULL THEN 0 ELSE 1 END TableDescExists,
				a.name AS ColName,
				isnull(g.[value],'') AS ColDesc,
				CASE WHEN g.Value IS NULL OR g.Value = NULL THEN 0 ELSE 1 END ColDescExists 
			FROM 
				syscolumns a
			left join 
				systypes b 
			on 
				a.xusertype=b.xusertype
			inner join 
				sysobjects d 
			on 
				a.id=d.id  and (d.xtype='U' OR d.xtype = 'V') and  d.name<>'dtproperties'
			left join 
				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
			LEFT JOIN sys.tables h ON  OBJECT_ID = d.id
			LEFT JOIN sys.schemas i ON h.schema_id= i.[schema_id]
			WHERE   
				i.name + '.' + d.name=@Schema + '.'+@TableName    --如果只查询指定表,加上此where条件，tablename是要查询的表名；去除where条件查询所有的表信息
				
			UNION ALL 

			SELECT  i.name AS SchemaName,
					d.name AS TableName,
					isnull(f.value,'') AS TableDesc,
					CASE WHEN f.Value IS NULL OR f.Value = NULL THEN 0 ELSE 1 END TableDescExists,
					a.name AS ColName,
					isnull(g.[value],'') AS ColDesc,
					CASE WHEN g.Value IS NULL OR g.Value = NULL THEN 0 ELSE 1 END ColDescExists  	FROM 
					syscolumns a
				left join 
					systypes b 
				on 
					a.xusertype=b.xusertype
				left join 
					sysobjects d 
				on 
					a.id=d.id  and (d.xtype='U' OR d.xtype = 'V') and  d.name<>'dtproperties'
				left join 
					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
				LEFT JOIN sys.views h ON  OBJECT_ID = d.id
				LEFT JOIN sys.schemas i ON h.schema_id= i.[schema_id]

					where 
					i.name + '.' + d.name= @Schema + '.'+@TableName    --如果只查询指定表,加上此where条件，tablename是要查询的表名；去除where条件查询所有的表信息
					
		) AS a  

	IF NOT EXISTS (
		SELECT 1 FROM sys.tables h 
		INNER JOIN sys.schemas i ON h.schema_id= i.[schema_id] WHERE h.name = @TableName AND i.name = @Schema
	) AND @Level1Type = 'Table'
	BEGIN
		--表不存在
		RETURN;
	END 

	IF ISNULL(@ColumnName,'') <> N'' AND @Type = N'Column' AND NOT EXISTS (SELECT 1 FROM #tmp_DescDt WHERE TableName = @TableName AND ColName = @ColumnName AND SchemaName = @Schema)
	BEGIN
		 --列不存在
		RETURN;
	END 

	SET @Description = ISNULL(@Description,'')
	
	IF @Type = N'Table' OR @Type = N'View'
	BEGIN
		IF EXISTS (SELECT 1 FROM #tmp_DescDt WHERE ISNULL(TableDesc,'') <> @Description) 
		BEGIN
			IF EXISTS(SELECT 1 FROM #tmp_DescDt WHERE TableDescExists = 1)
			BEGIN
				--修改表描述
				EXEC sys.sp_updateextendedproperty @name=N'MS_Description', @value=@Description , @level0type=N'SCHEMA',@level0name=@Schema, @level1type=@Level1Type,@level1name=@TableName
			END
			ELSE
            BEGIN
				--新增表描述
				EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=@Description , @level0type=N'SCHEMA',@level0name=@Schema, @level1type=@Level1Type,@level1name=@TableName
			END 
		END 
	END 
	
	IF @Type = N'Column'
	BEGIN
		IF EXISTS (SELECT 1 FROM #tmp_DescDt WHERE ISNULL(ColDesc,'') <> @Description AND ColName = @ColumnName)
		BEGIN
			IF EXISTS (SELECT 1 FROM  #tmp_DescDt WHERE ColName = @ColumnName AND ColDescExists = 1)
			BEGIN
				 --修改列描述
				EXEC sys.sp_updateextendedproperty @name=N'MS_Description', @value=@Description , @level0type=N'SCHEMA',@level0name=@Schema, @level1type=@Level1Type,@level1name=@TableName,@level2type=N'COLUMN',@level2name = @ColumnName
			END 
			ELSE
            BEGIN
				--新增表描述
				EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=@Description , @level0type=N'SCHEMA',@level0name=@Schema, @level1type=@Level1Type,@level1name=@TableName,@level2type=N'COLUMN',@level2name = @ColumnName
			END 
		END  
	END 

	if object_id('tempdb..#tmp_DescDt') is not null    
		 drop table #tmp_DescDt


GO