用表来管理SQLServer中的扩展属性(描述)
程序员文章站
2022-05-01 12:27:39
...
数据字典是个好东东,对于开发、维护非常重要。 但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