数据字典是个好东东，对于开发、维护非常重要。
但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_descIF 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]GOCREATE 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]GOCREATE table [dbo].[dc_util_column_desc]( [ID] [int] IDENTITY(1,[columnname] [varchar](100) NulL,[columnDesc] [nvarchar](200) NulL,CONSTRAINT [PK_dc_util_column_desc] PRIMARY KEY CLUSTERED ( [ID] ASC)WITH (PAD_INDEX = OFF,ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],CONSTRAINT [UQ_dc_util_column_desc_tablename_columnname] UNIQUE NONCLUSTERED ( [tablename] ASC,[columnname] ASC)WITH (PAD_INDEX = OFF,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] ASBEGIN 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 )ENDGO--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 =1AS 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<=@i_max BEGIN SELECT @t_name=tablename,@t_desc=tabledesc FROM @t1 WHERE rn=@i IF @overrIDeDesc=1 begin IF EXISTS(SELECT 1 FROM dc_util_table_desc WHERE tablename=@t_name) UPDATE dc_util_table_desc SET tableDesc = @t_desc WHERE tablename=@t_name ELSE INSERT INTO dc_util_table_desc(tablename,tableDesc) VALUES (@t_name,@t_desc) END ELSE BEGIN IF NOT EXISTS(SELECT 1 FROM dc_util_table_desc WHERE tablename=@t_name) INSERT INTO dc_util_table_desc(tablename,@t_desc) END set @i=@i+1 ENDENDGO--2.3 存储过程IF EXISTS (SELECT * FROM sys.objects WHERE object_ID = OBJECT_ID(N'[dbo].[Proc_Util_Desc_GetColumnnameToDesctable]') AND type in (N'P',N'PC')) DROP PROCEDURE [dbo].[Proc_Util_Desc_GetColumnnameToDesctable]GO-- =============================================-- Author: -- Create date: 2014-05-29-- Description: 将以 @tablePrefix 为前缀的表名对应的列和列对应的扩展属性 insert 到 dc_util_column_desc 表中去.-- @tablePrefix 如果为 '' 或者 null， 则为全部表(默认为null)-- @overrIDeDesc : 如果已有记录存在，是否覆盖原记录的扩展属性 (默认为1)-- =============================================CREATE procedure [dbo].[Proc_Util_Desc_GetColumnnameToDesctable] @tablePrefix VARCHAR(100) =null,ColUMNname VARCHAR(100),columndesc NVARCHAR(200)) --插入以 @tablePrefix 为前缀的表到@t1 INSERT INTO @t1 ( tablename,ColUMNname,columndesc ) SELECT convert(varchar(100),convert(varchar(100),c.name),convert(nvarchar(200),p.value) FROM sys.tables AS t left JOIN sys.columns c ON t.object_ID = c.object_ID left JOIN sys.extended_propertIEs AS p ON p.major_ID = t.object_ID AND p.minor_ID = c.column_ID 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 @col_name VARCHAR(100) DECLARE @col_desc NVARCHAR(200) SET @i=1 SELECT @i_max=COUNT(1) FROM @t1 WHILE @i<=@i_max BEGIN SELECT @t_name=tablename,@col_name=ColUMNname,@col_desc=columndesc FROM @t1 WHERE rn=@i IF @overrIDeDesc=1 begin IF EXISTS(SELECT 1 FROM dc_util_column_desc WHERE tablename=@t_name AND columnname=@col_name) UPDATE dc_util_column_desc SET columnDesc = @col_desc WHERE tablename=@t_name AND columnname=@col_name ELSE INSERT INTO dc_util_column_desc(tablename,columnname,columnDesc) VALUES (@t_name,@col_name,@col_desc) END ELSE BEGIN IF NOT EXISTS(SELECT 1 FROM dc_util_column_desc WHERE tablename=@t_name AND columnname=@col_name ) INSERT INTO dc_util_column_desc(tablename,@col_desc) END set @i=@i+1 ENDENDGO--2.4 存储过程IF EXISTS (SELECT * FROM sys.objects WHERE object_ID = OBJECT_ID(N'[dbo].[Proc_Util_Desc_SetDescTotable]') AND type in (N'P',N'PC')) DROP PROCEDURE [dbo].[Proc_Util_Desc_SetDescTotable]GO-- =============================================-- Author: -- Create date: 2014-05-29-- Description: 将 dc_util_table_desc 表中的 tableDesc 写到对应表的扩展属性-- @tablePrefix 为表前缀 如果为 '' 或者 null， 则为全部表(默认为null)-- =============================================CREATE PROCEDURE [dbo].[Proc_Util_Desc_SetDescTotable] @tablePrefix varchar(100) = nullASBEGIN SET NOCOUNT ON --删除表中无效的数据 exec Proc_Util_Desc_DeleteInvalIDData --定义表变量 DECLARE @t1 table(rn int IDENTITY(1,tabledesc NVARCHAR(200)) --插入需要修改扩展属性的数据到表变量@t1 INSERT INTO @t1 ( tablename,tabledesc ) SELECT tablename,tabledesc FROM dc_util_table_desc WHERE ISNulL(@tablePrefix,'')='' OR tablename 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<=@i_max BEGIN SELECT @t_name=tablename,@t_desc=tabledesc FROM @t1 WHERE rn=@i IF isnull(@t_desc,'')='' BEGIN SET @i=@i+1 CONTINUE END --如果表上存在MS_Description就update,不存在就insert IF EXISTS (SELECT p.value FROM sys.tables AS t left JOIN sys.extended_propertIEs AS p ON p.major_ID = t.object_ID WHERE t.SCHEMA_ID IN (SELECT SCHEMA_ID FROM sys.schemas WHERE name = 'dbo') AND p.minor_ID = 0 AND p.class = 1 AND p.name = 'MS_Description' AND t.name =@t_name) BEGIN EXEC sp_updateextendedproperty @name = N'MS_Description',@value = @t_desc,@level0type = N'Schema',@level0name = 'dbo',@level1type = N'table',@level1name = @t_name END ELSE BEGIN EXEC sp_addextendedproperty @name = N'MS_Description',@level1name = @t_name END SET @i=@i+1 ENDENDGO--2.5 存储过程IF EXISTS (SELECT * FROM sys.objects WHERE object_ID = OBJECT_ID(N'[dbo].[Proc_Util_Desc_SetDescToColumn]') AND type in (N'P',N'PC')) DROP PROCEDURE [dbo].[Proc_Util_Desc_SetDescToColumn]GO-- =============================================-- Author: -- Create date: 2014-05-29-- Description: 将dc_util_column_desc 表中的 columnDesc 写到对应表对应列的扩展属性-- @tablePrefix 为表前缀 如果为 '' 或者 null， 则为全部表(默认为null)-- =============================================CREATE PROCEDURE [dbo].[Proc_Util_Desc_SetDescToColumn] @tablePrefix varchar(100) = nullASBEGIN SET NOCOUNT ON --删除表中无效的数据 exec Proc_Util_Desc_DeleteInvalIDData --定义表变量 DECLARE @t1 table(rn int IDENTITY(1,columnname VARCHAR(100),columndesc NVARCHAR(200)) -- 插入需要修改扩展属性的数据到表变量@t1 INSERT INTO @t1 ( tablename,columnname,columndesc ) SELECT tablename,columndesc FROM dc_util_column_desc WHERE ISNulL(@tablePrefix,'')='' or tablename liKE +@tablePrefix+'%' --循环表变量中的数据 DECLARE @i INT DECLARE @i_max INT DECLARE @t_name VARCHAR(100) DECLARE @col_name VARCHAR(100) DECLARE @col_desc NVARCHAR(200) SET @i=1 SELECT @i_max=COUNT(1) FROM @t1 WHILE @i<=@i_max BEGIN SELECT @t_name=tablename,@col_name=columnname,@col_desc=columndesc FROM @t1 WHERE rn=@i IF ISNulL(@col_desc,'')='' BEGIN SET @i=@i+1 CONTINUE END --如果列上存在MS_Description就update,不存在就add IF EXISTS (SELECT p.value FROM sys.tables AS t left JOIN sys.extended_propertIEs AS p ON p.major_ID = t.object_ID left JOIN sys.columns c ON t.object_ID=c.object_ID AND c.column_ID=p.minor_ID WHERE t.SCHEMA_ID IN (SELECT SCHEMA_ID FROM sys.schemas WHERE name = 'dbo') AND p.class = 1 AND p.minor_ID!=0 AND p.name = 'MS_Description' AND t.name = @t_name AND c.name = @col_name) BEGIN EXEC sp_updateextendedproperty @name = N'MS_Description',@value = @col_desc,@level1name = @t_name,@level2type = N'Column',@level2name = @col_name END ELSE BEGIN EXEC sp_addextendedproperty @name = N'MS_Description',@level2name = @col_name END SET @i=@i+1 ENDENDGO--3.1 触发器 IF EXISTS (SELECT * FROM sys.triggers WHERE object_ID = OBJECT_ID(N'[dbo].[Trig_dc_util_table_desc_I_U]')) DROP TRIGGER [dbo].[Trig_dc_util_table_desc_I_U]GO-- =============================================-- Author: -- Create date: 2014-05-29-- Description: 将记录更新到对应表的扩展属性-- =============================================CREATE TRIGGER [dbo].[Trig_dc_util_table_desc_I_U] ON [dbo].[dc_util_table_desc] AFTER INSERT,UPDATEAS BEGIN --触发Proc_Util_SetDescTotable 更新表描述 DECLARE @m VARCHAR(100) SELECT @m=tablename FROM inserted EXEC Proc_Util_Desc_SetDescTotable @[email protected] 触发器IF EXISTS (SELECT * FROM sys.triggers WHERE object_ID = OBJECT_ID(N'[dbo].[Trig_dc_util_column_desc_I_U]')) DROP TRIGGER [dbo].[Trig_dc_util_column_desc_I_U]GO-- =============================================-- Author: -- Create date: 2014-05-29-- Description: 将记录更新到对应列的扩展属性-- =============================================CREATE TRIGGER [dbo].[Trig_dc_util_column_desc_I_U] ON [dbo].[dc_util_column_desc] AFTER INSERT,UPDATEAS BEGIN --触发Proc_Util_SetDescToColumn 去更新列描述 DECLARE @m VARCHAR(100) SELECT @m=tablename FROM inserted EXEC Proc_Util_Desc_SetDescToColumn @[email protected] 查看表的描述IF EXISTS (SELECT * FROM sys.objects WHERE object_ID = OBJECT_ID(N'[dbo].[Fun_GettableStru]') AND type in (N'FN',N'IF',N'TF',N'FS',N'FT')) DROP FUNCTION [dbo].[Fun_GettableStru]GO-- =============================================-- Author: -- Create date: 2014-03-27-- Description: 获取表结构-- Demo: select * from [dbo].[Fun_GettableStru]('表名')-- =============================================CREATE FUNCTION [dbo].[Fun_GettableStru] ( @tablename NVARCHAR(MAX))RETURNS table ASRETURN ( SELECT ac.column_ID AS columnID,AC.[name] AS columnname,TY.[name] AS dataType,AC.max_length AS maxLength,AC.[is_nullable] isNullable,CASE WHEN AC.[name] in (SELECT ColUMN_name = convert(sysname,c.name) from sysindexes i,syscolumns c,sysobjects o where o.ID = object_ID(@tablename) and o.ID = c.ID and o.ID = i.ID and (i.status & 0x800) = 0x800 and ( c.name = index_col (@tablename,i.indID,1) or c.name = index_col (@tablename,2) or c.name = index_col (@tablename,3) or c.name = index_col (@tablename,4) or c.name = index_col (@tablename,5) or c.name = index_col (@tablename,6) or c.name = index_col (@tablename,7) or c.name = index_col (@tablename,8) or c.name = index_col (@tablename,9) or c.name = index_col (@tablename,10) or c.name = index_col (@tablename,11) or c.name = index_col (@tablename,12) or c.name = index_col (@tablename,13) or c.name = index_col (@tablename,14) or c.name = index_col (@tablename,15) or c.name = index_col (@tablename,16) )) THEN 1 ELSE 0 END AS isPK,CASE WHEN AC.[name] IN ( SELECT t1.name FROM ( SELECT col.name,f.constID AS temp FROM syscolumns col,sysforeignkeys f WHERE f.fkeyID = col.ID AND f.fkey = col.colID AND f.constID IN (SELECT disTINCT(ID) FROM sysobjects WHERE OBJECT_name(parent_obj) = @tablename AND xtype = 'F') ) AS t1,( SELECT OBJECT_name(f.rkeyID) AS rtablename,col.name,f.constID AS temp FROM syscolumns col,sysforeignkeys f WHERE f.rkeyID = col.ID AND f.rkey = col.colID AND f.constID IN (SELECT disTINCT(ID) FROM sysobjects WHERE OBJECT_name(parent_obj) = @tablename AND xtype = 'F') ) AS t2 WHERE t1.temp = t2.temp ) THEN 1 ELSE 0 END AS isFK,(SELECT ColUMNPROPERTY( OBJECT_ID(@tablename),ac.name,'IsIDentity')) AS isIDentity,ISNulL(t2.[DESCRIPTION],'') AS [columnDesc],ISNulL(( SELECT ISNulL(VALUE,'') FROM sys.extended_propertIEs ex_p WHERE ex_p.minor_ID = 0 AND ex_p.major_ID = t.OBJECT_ID ),'') AS [tableDesc]FROM sys.[tables] AS T INNER JOIN sys.[all_columns] AC ON T.[object_ID] = AC.[object_ID] INNER JOIN sys.[types] TY ON AC.[system_type_ID] = TY.[system_type_ID] AND AC.[user_type_ID] = TY.[user_type_ID] left JOIN ( SELECT disTINCT(sys.columns.name),( SELECT VALUE FROM sys.extended_propertIEs WHERE sys.extended_propertIEs.major_ID = sys.columns.object_ID AND sys.extended_propertIEs.minor_ID = sys.columns.column_ID ) AS DESCRIPTION FROM sys.columns,sys.tables,sys.types WHERE sys.columns.object_ID = sys.tables.object_ID AND sys.columns.system_type_ID = sys.types.system_type_ID AND sys.tables.name = @tablename ) AS t2 ON AC.name=t2.nameWHERE T.[is_ms_shipped] = 0 AND T.name=@tablename)GO总结
以上是内存溢出为你收集整理的用表来管理SQLServer中的扩展属性(描述)全部内容，希望文章能够帮你解决用表来管理SQLServer中的扩展属性(描述)所遇到的程序开发问题。
如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。
欢迎分享，转载请注明来源：内存溢出
微信扫一扫
支付宝扫一扫
评论列表（0条）