欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

用表来管理SQLServer中的扩展属性(描述)

程序员文章站 2022-06-01 23:50:29
...

数据字典是个好东东,对于开发、维护非常重要。 但Sql Server中写描述确实不方便,如何化繁为简、批量地增加修改扩展属性呢? 增加2个表和5个存储过程、2个触发器、1个表函数就好了。 把下面的SQL执行一遍生成相关的对象, 然后执行一下: 1. EXECProc_Util_

数据字典是个好东东,对于开发、维护非常重要。

但Sql Server中写描述确实不方便,如何化繁为简、批量地增加修改扩展属性呢?

增加2个表和5个存储过程、2个触发器、1个表值函数就好了。

把下面的SQL执行一遍生成相关的对象, 然后执行一下:

1. EXEC Proc_Util_Desc_GetColumnNameToDescTable , 生成表的描述对应记录

2. EXEC Proc_Util_Desc_GetTableNameToDescTable, 生成列的描述对应记录

3. 查看, 修改一下 dc_util_column_desc 中的某个表某个列的描述,

4. 查看: select * from [dbo].[Fun_GetTableStru]('表名')

爽吧?!


--1.1 建表(存放表的描述):dbo.dc_util_table_desc
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[dc_util_table_desc]') AND type in (N'U'))
	DROP TABLE [dbo].[dc_util_table_desc]
GO
CREATE TABLE [dbo].[dc_util_table_desc](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[tableName] [varchar](100) NULL,
	[tableDesc] [nvarchar](200) NULL,
 CONSTRAINT [PK_dc_util_table_desc] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

--1.2 建表(存放列的描述):[dc_util_column_desc]
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[dc_util_column_desc]') AND type in (N'U'))
	DROP TABLE [dbo].[dc_util_column_desc]
GO
CREATE TABLE [dbo].[dc_util_column_desc](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[tableName] [varchar](100) NULL,
	[columnName] [varchar](100) NULL,
	[columnDesc] [nvarchar](200) NULL,
 CONSTRAINT [PK_dc_util_column_desc] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
 CONSTRAINT [UQ_dc_util_column_desc_tableName_columnName] UNIQUE NONCLUSTERED 
(
	[tableName] ASC,
	[columnName] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

--2.1 存储过程
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Proc_Util_Desc_DeleteInvalidData]') AND type in (N'P', N'PC'))
	DROP PROCEDURE [dbo].[Proc_Util_Desc_DeleteInvalidData]
GO
-- =============================================
-- Author:		yenange
-- Create date: 2014-05-29
-- Description:	删除 dc_util_table_desc 表和 
--              dc_util_column_desc 表中不正确的数据
-- =============================================
CREATE PROCEDURE [dbo].[Proc_Util_Desc_DeleteInvalidData] 
AS
BEGIN
	SET NOCOUNT ON;
	--删除 dc_util_table_desc 中的无效数据
	DELETE FROM dbo.dc_util_table_desc WHERE NOT EXISTS (
		SELECT 1 FROM sys.tables T WHERE dbo.dc_util_table_desc.tableName=T.name
    ) 
    --删除 dc_util_column_desc 中的无效数据
    DELETE 
	FROM   dbo.dc_util_column_desc
	WHERE  NOT EXISTS (SELECT 1 FROM sys.tables t INNER JOIN sys.columns c ON  t.object_id = c.object_id
							WHERE  t.SCHEMA_ID IN (SELECT SCHEMA_ID FROM sys.schemas WHERE  NAME = 'dbo')
								AND dbo.dc_util_column_desc.tableName=t.name AND dbo.dc_util_column_desc.columnName=c.name	
						)
END
GO

--2.2 存储过程
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Proc_Util_Desc_GetTableNameToDescTable]') AND type in (N'P', N'PC'))
	DROP PROCEDURE [dbo].[Proc_Util_Desc_GetTableNameToDescTable]
GO
-- =============================================
-- Author:		
-- Create date: 2014-05-29
-- Description:	将以 @tablePrefix 为前缀的表名和表对应的扩展属性 insert 到 dc_util_table_desc 表中去.
--              @tablePrefix 如果为 '' 或者 null, 则为全部表(默认为null)
--              @overrideDesc : 如果已有记录存在,是否覆盖原记录的扩展属性 (默认为1)
-- =============================================
CREATE procedure [dbo].[Proc_Util_Desc_GetTableNameToDescTable] 
	@tablePrefix VARCHAR(100) =null, 
	@overrideDesc BIT =1
AS 
BEGIN
	SET NOCOUNT ON
	--删除表中无效的数据
	exec Proc_Util_Desc_DeleteInvalidData
	
	DECLARE @t1 TABLE(rn int IDENTITY(1,1),tablename VARCHAR(100),tabledesc NVARCHAR(200))
	--插入以 @tablePrefix 为前缀的表到@t1
	INSERT INTO @t1
	(
		tablename,
		tabledesc
	)
	SELECT convert(VARCHAR(100),t.name),
		   convert (nvarchar(200),p.value)
	FROM   sys.tables                         AS t
		   LEFT JOIN sys.extended_properties  AS p
				ON  p.major_id = t.object_id
				AND p.minor_id = 0
				AND p.class = 1
				AND p.name = 'MS_Description'
	WHERE  t.SCHEMA_ID IN (SELECT SCHEMA_ID
						   FROM   sys.schemas
						   WHERE  NAME = 'dbo')
	 AND (ISNULL(@tablePrefix,'')='' or t.name LIKE +@tablePrefix+'%' )
	
	DECLARE @i INT
	DECLARE @i_max INT
	DECLARE @t_name VARCHAR(100)
	DECLARE @t_desc NVARCHAR(200)
	SET @i=1
	SELECT @i_max=COUNT(1) FROM @t1
	WHILE @i