用表來管理SQLServer中的擴充屬性(描寫敘述)

標籤：

資料字典是個好東東，對於開發、維護很重要。

但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 [email protected]+'%' )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<[email protected]_maxBEGINSELECT @t_name=tablename,@t_desc=tabledesc FROM @t1 WHERE [email protected]IF @overrideDesc=1beginIF EXISTS(SELECT 1 FROM dc_util_table_desc WHERE [email protected]_name)UPDATE dc_util_table_desc SET tableDesc = @t_desc WHERE [email protected]_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 [email protected]_name)INSERT INTO dc_util_table_desc(tablename,tableDesc) VALUES (@t_name,@t_desc)ENDset @[email protected]+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 [email protected]+'%')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<[email protected]_maxBEGINSELECT @t_name=tablename,@col_name=COLUMNNAME,@col_desc=columndesc FROM @t1 WHERE [email protected]IF @overrideDesc=1beginIF EXISTS(SELECT 1 FROM dc_util_column_desc WHERE [email protected]_name AND [email protected]_name)UPDATE dc_util_column_desc SET columnDesc = @col_desc WHERE [email protected]_name AND [email protected]_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 [email protected]_name AND [email protected]_name )INSERT INTO dc_util_column_desc(tablename,columnName,columnDesc) VALUES (@t_name,@col_name,@col_desc)ENDset @[email protected]+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 [email protected]+'%'--迴圈表變數中的資料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<[email protected]_maxBEGINSELECT @t_name=tablename,@t_desc=tabledesc FROM @t1 WHERE [email protected]IF isnull(@t_desc,'')=''BEGINSET @[email protected]+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 [email protected]_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 @[email protected]+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 [email protected]+'%'--迴圈表變數中的資料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<[email protected]_maxBEGINSELECT @t_name=tablename,@col_name=columnname,@col_desc=columndesc FROM @t1 WHERE [email protected]IF ISNULL(@col_desc,'')=''BEGINSET @[email protected]+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 @[email protected]+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 @[email protected]END--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 @[email protected]END--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 [email protected])GO

用表來管理SQLServer中的擴充屬性(描寫敘述)