用表来管理SQLServer中的扩展属性(描述)
来源：互联网 发布：许昌学历网络教育报考 编辑：程序博客网 时间：2024/06/06 05:24
数据字典是个好东东，对于开发、维护非常重要。
但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,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] ASBEGINSET 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_descWHERE NOT EXISTS (SELECT 1 FROM sys.tables t INNER JOIN sys.columns c ON t.object_id = c.object_idWHERE 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 BEGINSET NOCOUNT ON--删除表中无效的数据exec Proc_Util_Desc_DeleteInvalidDataDECLARE @t1 TABLE(rn int IDENTITY(1,1),tablename VARCHAR(100),tabledesc NVARCHAR(200))--插入以 @tablePrefix 为前缀的表到@t1INSERT 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 pON p.major_id = t.object_idAND p.minor_id = 0AND p.class = 1AND 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 INTDECLARE @i_max INTDECLARE @t_name VARCHAR(100)DECLARE @t_desc NVARCHAR(200)SET @i=1SELECT @i_max=COUNT(1) FROM @t1WHILE @i<=@i_maxBEGINSELECT @t_name=tablename,@t_desc=tabledesc FROM @t1 WHERE rn=@iIF @overrideDesc=1beginIF EXISTS(SELECT 1 FROM dc_util_table_desc WHERE tableName=@t_name)UPDATE dc_util_table_desc SET tableDesc = @t_desc WHERE tableName=@t_nameELSE INSERT INTO dc_util_table_desc(tablename,tableDesc) VALUES (@t_name,@t_desc)ENDELSE BEGINIF NOT EXISTS(SELECT 1 FROM dc_util_table_desc WHERE tableName=@t_name)INSERT INTO dc_util_table_desc(tablename,tableDesc) VALUES (@t_name,@t_desc)ENDset @i=@i+1ENDENDGO--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, @overrideDesc BIT =1AS BEGINSET NOCOUNT ON--删除表中无效的数据exec Proc_Util_Desc_DeleteInvalidDataDECLARE @t1 TABLE(rn int IDENTITY(1,1),tablename VARCHAR(100),COLUMNNAME VARCHAR(100),columndesc NVARCHAR(200))--插入以 @tablePrefix 为前缀的表到@t1INSERT INTO @t1(tablename,COLUMNNAME,columndesc)SELECT convert(varchar(100),t.name) , convert(varchar(100),c.name) , convert(nvarchar(200),p.value) FROM sys.tables AS t LEFT JOIN sys.columns cON t.object_id = c.object_id LEFT JOIN sys.extended_properties AS pON p.major_id = t.object_idAND p.minor_id = c.column_idAND p.class = 1AND 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 INTDECLARE @i_max INTDECLARE @t_name VARCHAR(100)DECLARE @col_name VARCHAR(100)DECLARE @col_desc NVARCHAR(200)SET @i=1SELECT @i_max=COUNT(1) FROM @t1WHILE @i<=@i_maxBEGINSELECT @t_name=tablename,@col_name=COLUMNNAME,@col_desc=columndesc FROM @t1 WHERE rn=@iIF @overrideDesc=1beginIF 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_nameELSE INSERT INTO dc_util_column_desc(tablename,columnName,columnDesc) VALUES (@t_name,@col_name,@col_desc)ENDELSE BEGINIF NOT EXISTS(SELECT 1 FROM dc_util_column_desc WHERE tableName=@t_name AND columnName=@col_name )INSERT INTO dc_util_column_desc(tablename,columnName,columnDesc) VALUES (@t_name,@col_name,@col_desc)ENDset @i=@i+1ENDENDGO--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) = nullASBEGINSET NOCOUNT ON--删除表中无效的数据exec Proc_Util_Desc_DeleteInvalidData--定义表变量DECLARE @t1 TABLE(rn int IDENTITY(1,1),tablename VARCHAR(100),tabledesc NVARCHAR(200))--插入需要修改扩展属性的数据到表变量@t1INSERT INTO @t1(tablename,tabledesc)SELECT tablename,tabledesc FROM dc_util_table_desc WHERE ISNULL(@tablePrefix,'')='' OR tablename LIKE +@tablePrefix+'%'--循环表变量中的数据DECLARE @i INTDECLARE @i_max INTDECLARE @t_name VARCHAR(100)DECLARE @t_desc NVARCHAR(200)SET @i=1SELECT @i_max=COUNT(1) FROM @t1WHILE @i<=@i_maxBEGINSELECT @t_name=tablename,@t_desc=tabledesc FROM @t1 WHERE rn=@iIF isnull(@t_desc,'')=''BEGINSET @i=@i+1CONTINUEEND--如果表上存在MS_Description就update,不存在就insertIF EXISTS (SELECT p.valueFROM sys.tables AS t LEFT JOIN sys.extended_properties AS pON p.major_id = t.object_idWHERE t.SCHEMA_ID IN (SELECT SCHEMA_ID FROM sys.schemas WHERE NAME = 'dbo')ANDp.minor_id = 0ANDp.class = 1ANDp.name = 'MS_Description'ANDt.name =@t_name)BEGINEXEC sp_updateextendedproperty @name = N'MS_Description',@value = @t_desc,@level0type = N'Schema', @level0name = 'dbo',@level1type = N'Table', @level1name = @t_nameENDELSEBEGINEXEC sp_addextendedproperty @name = N'MS_Description',@value = @t_desc,@level0type = N'Schema', @level0name = 'dbo',@level1type = N'Table', @level1name = @t_nameENDSET @i=@i+1ENDENDGO--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) = nullASBEGINSET NOCOUNT ON--删除表中无效的数据exec Proc_Util_Desc_DeleteInvalidData--定义表变量DECLARE @t1 TABLE(rn int IDENTITY(1,1),tablename VARCHAR(100),columnname VARCHAR(100),columndesc NVARCHAR(200))-- 插入需要修改扩展属性的数据到表变量@t1INSERT INTO @t1(tablename,columnname,columndesc)SELECT tablename,columnname,columndesc FROM dc_util_column_desc WHERE ISNULL(@tablePrefix,'')='' or tablename LIKE +@tablePrefix+'%'--循环表变量中的数据DECLARE @i INTDECLARE @i_max INTDECLARE @t_name VARCHAR(100)DECLARE @col_name VARCHAR(100)DECLARE @col_desc NVARCHAR(200)SET @i=1SELECT @i_max=COUNT(1) FROM @t1WHILE @i<=@i_maxBEGINSELECT @t_name=tablename,@col_name=columnname,@col_desc=columndesc FROM @t1 WHERE rn=@iIF ISNULL(@col_desc,'')=''BEGINSET @i=@i+1CONTINUEEND--如果列上存在MS_Description就update,不存在就addIF EXISTS (SELECT p.valueFROM 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_idWHERE 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)BEGINEXEC sp_updateextendedproperty @name = N'MS_Description',@value = @col_desc,@level0type = N'Schema', @level0name = 'dbo',@level1type = N'Table', @level1name = @t_name,@level2type = N'Column', @level2name = @col_nameENDELSEBEGINEXEC sp_addextendedproperty @name = N'MS_Description',@value = @col_desc,@level0type = N'Schema', @level0name = 'dbo',@level1type = N'Table', @level1name = @t_name,@level2type = N'Column', @level2name = @col_nameENDSET @i=@i+1ENDENDGO--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 insertedEXEC Proc_Util_Desc_SetDescToTable @tablePrefix=@mEND--3.2 触发器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 insertedEXEC Proc_Util_Desc_SetDescToColumn @tablePrefix=@mEND--4.1 查看表的描述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 (SELECTac.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.idand o.id = i.idand (i.status & 0x800) = 0x800and (c.name = index_col (@tableName, i.indid, 1) orc.name = index_col (@tableName, i.indid, 2) orc.name = index_col (@tableName, i.indid, 3) orc.name = index_col (@tableName, i.indid, 4) orc.name = index_col (@tableName, i.indid, 5) orc.name = index_col (@tableName, i.indid, 6) orc.name = index_col (@tableName, i.indid, 7) orc.name = index_col (@tableName, i.indid, 8) orc.name = index_col (@tableName, i.indid, 9) orc.name = index_col (@tableName, i.indid, 10) orc.name = index_col (@tableName, i.indid, 11) orc.name = index_col (@tableName, i.indid, 12) orc.name = index_col (@tableName, i.indid, 13) orc.name = index_col (@tableName, i.indid, 14) orc.name = index_col (@tableName, i.indid, 15) orc.name = index_col (@tableName, i.indid, 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 0 0
- 用表来管理SQLServer中的扩展属性(描述)
- 如何用表来管理SQLServer中的扩展属性
- SQL Server中的扩展属性
- Python中的属性管理
- Python中的属性管理
- Python中的属性管理
- studio开发安卓中的属性描述
- Python 中的属性访问与描述符
- javascript中的location对象属性及描述。
- 对象属性管理和描述符的使用
- UIView中的clipsTobounds属性及扩展
- UIView中的clipsTobounds属性及扩展
- UnityEditor扩展 - 编辑器中的内置属性Attribute
- SQLServer管理
- ICA扩展描述
- ICA算法扩展描述
- ICA扩展描述
- ICA扩展描述
- OpenCV中霍夫变换相关的知识点
- iOS中的下拉刷新SVPullToRefresh
- android-----JNI学习 helloworld
- JVM最多能创建多少个线程: unable to create new native thread
- 浅谈hadoop中mapreduce的文件分发
- 用表来管理SQLServer中的扩展属性(描述)
- [Android UI控件] AndroidSlidingUpPanel 分析
- Hive学习之Hive数据类型
- for 循环遍历
- 心灵的撞击
- mysql 索引(index)
- 【冰心奖】作文投稿软件的开发感想
- 什么是长尾关键词？
- 盈一份恬淡，安然一世春秋！
