浅谈数据字典的设计(SQL Server 2005)

数据字典：此文指表的中文意思以及字段的中文含义。

另：数据字典的结构有参考金蝶K3的表结构，而脚本则为自己编写。

我们在设计后台数据库时，一般表名和字段名是英文 。且一般每个人都只对自己所属模块熟，而在需要用到其他模块的表时，则需要询问其他人。这样比较麻烦，且如果当初设计此表的人已经离开，则还需看代码，这样就更不方便了。因此对于一个好的数据库来说设计数据字典则非常必要。

设计数据字典要有两个表，一个是表清单（TableDescription），一个是字段表（FieldDescription）。

表清单用来存放所有的非系统表，而字段表用来存放所有的字段以及数据类型及中文含义。

设计这两个表的脚本如下：

/****** 对象 : Table [dbo].[TableDescription]     ******/

CREATE TABLE [dbo] .[TableDescription] (

        [TableID] [int] IDENTITY (1 ,1 ) NOT FOR REPLICATION NOT NULL,

        [TableName] [nvarchar] (50 ) NOT NULL, -- 表名

        [FDescription] [nvarchar] (100 ) NULL, -- 中文说明

        [FDescription_en] [nvarchar] (200 ) NULL, -- 英文说明

  CONSTRAINT [PK_TableDescription_TableID] PRIMARY KEY CLUSTERED

(

        [TableID] ASC

) WITH (PAD_INDEX = OFF , STATISTICS_NORECOMPUTE = OFF , IGNORE_DUP_KEY = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY]

) ON [PRIMARY]

 

-- 添加维一约束

ALTER TABLE TableDescription ADD CONSTRAINT un_TableDescription_TableName UNIQUE (TableName )

 

/****** 对象 : Table [dbo].[FieldDescription]    ******/

CREATE TABLE [dbo] .[FieldDescription] (

        [FieldID] [int] IDENTITY (1 ,1 ) NOT FOR REPLICATION NOT NULL,

        [TableID] INT  NOT NULL,

        [FieldName] [nvarchar] (50 ) NOT NULL , -- 字段名称

        [FieldType] [nvarchar] (20 ) NOT NULL, -- 字段类型

        [Prec] [smallint] NULL, -- 长度

        [Scale] [smallint] NULL, -- 小数位数

        [FDescription] [nvarchar] (100 ) NULL,               -- 中文说明

        [FDescription_en] [nvarchar] (200 ) NULL,           -- 英文说明

  CONSTRAINT [PK_t_TableDescription] PRIMARY KEY NONCLUSTERED

(

        [TableID] ASC ,

        [FieldName] ASC

) WITH (PAD_INDEX = OFF , STATISTICS_NORECOMPUTE = OFF , IGNORE_DUP_KEY = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY]

) ON [PRIMARY]

 

-- 添加外键约束

ALTER TABLE [dbo] .[FieldDescription] WITH CHECK ADD CONSTRAINT [FK_FieldDescription_TableDescription] FOREIGN KEY ([TableID] )

REFERENCES [dbo] .[TableDescription] ([TableID] )

ON UPDATE CASCADE

ON DELETE CASCADE

GO

  

 

我们新建、更新、删除表时则需要更新上述两表的相应内容。如果第次都手工去修改则非常麻烦，为此我们可以这样做：数据库设计人员在设计表时需要在表下面的【说明】写上相应的中文说明。然后编写存储过程:sp_UpdateDD

USE master

GO

 

IF EXISTS( SELECT * FROM sysobjects WHERE NAME = 'sp_UpdateDD' AND type = 'P' AND SCHEMA_NAME (uid )= 'dbo' )

  DROP PROC dbo .sp_UpdateDD

GO

 

/*************************

PROC:sp_UpdateDD   

CREATE BY:Mark Yao  

CREATE Date:2008/1/19

USE: 更新資料字典   

INPUT:

OUTPUT:

Table List:

   [TableDescription] ：表描述

   [FieldDescription] ：字段描述

Eg:Exec sp_UpdateDD   

*************************/

CREATE PROC dbo .sp_UpdateDD

AS

  SET NOCOUNT ON

 

  DECLARE @TableID  INT ,    -- 表 ID

           @strTable NVARCHAR (100 ), -- 表名    

           @Fields   INT , -- 字段數　

           @Ver      INT --SQL SERVER 的版本号　   

                    

  BEGIN TRAN

 

  --1.1 刪除已經不存在的表

  DELETE dbo .TableDescription

  WHERE TABLENAME NOT IN ( SELECT SCHEMA_NAME (uid )+ '.' + NAME

                           FROM   SYSOBJECTS

                           WHERE  TYPE = 'U' )

                         

  --1.2 將新增的表插入到： TableDescription

  INSERT INTO dbo .TableDescription

             (TABLENAME )

  SELECT schema_name (uid )+ '.' + NAME

  FROM   SYSOBJECTS

  WHERE  TYPE = 'u'

         AND schema_name (uid )+ '.' + NAME NOT IN ( SELECT TABLENAME

                          FROM   dbo .TableDescription )

                        

  --2. 更新表結構到 FieldDescription

  SELECT TABLENAME =SCHEMA_NAME (d .uid )+ '.' + OBJECT_NAME (A .ID ),

         SN = A .COLORDER ,

         FIELDNAME = A . NAME ,

         FIELDTYPE = B . NAME ,

         PREC = CASE B . NAME

                  WHEN 'nvarchar' THEN A .LENGTH / 2

                  ELSE A .LENGTH

                END ,

         SCALE = CASE

                   WHEN A .XTYPE = 61 THEN 0

                   ELSE ISNULL ( COLUMNPROPERTY (A .ID ,A . NAME , 'scale' ),0 )

                 END

  INTO   #AA

  FROM   SYSCOLUMNS A

         LEFT JOIN SYSTYPES B

           ON A .XUSERTYPE = B .XUSERTYPE

         INNER JOIN SYSOBJECTS D

           ON A .ID = D .ID

              AND D .XTYPE = 'U'

              AND D . NAME <> 'dtproperties'

         LEFT JOIN SYSCOMMENTS E

           ON A .CDEFAULT = E .ID

                          

  SELECT B .TABLEID ,

         A .*

  INTO   #BB

  FROM   #AA A ,

         dbo .TableDescription B

  WHERE A .TABLENAME = B .TABLENAME

 

  -- 导出已经不存在的字段清单

  SELECT a .FieldID

   INTO #Field

   FROM dbo .FieldDescription a left join #BB b

   ON LTRIM ( STR (a .TABLEID )) + a .FIELDNAME = LTRIM ( STR (b .TABLEID )) + b .FIELDNAME

   WHERE ( LTRIM ( STR (b .TABLEID )) + b .FIELDNAME ) IS NULL

 

 

  -- 删除已经不存在的字段

  DELETE dbo .FieldDescription

  WHERE FieldID IN ( SELECT FieldID

   FROM #Field )

               

  -- 新增的字段

   SELECT b .* INTO #NewField

   FROM dbo .FieldDescription a right join #BB b

   ON LTRIM ( STR (a .TABLEID )) + a .FIELDNAME = LTRIM ( STR (b .TABLEID )) + b .FIELDNAME

   WHERE ( LTRIM ( STR (a .TABLEID )) + a .FIELDNAME ) IS NULL

 

                               

  -- 插入新增的字段

  INSERT dbo .FieldDescription

        (TABLEID ,

         FIELDNAME ,

         FIELDTYPE ,

         PREC ,

         SCALE )

  SELECT TABLEID ,

         FIELDNAME ,

         FIELDTYPE ,

         PREC ,

         SCALE

  FROM   #NewField

 

                                               

  -- 获取 SQL SERVER 版本号

  SELECT @Ver = CONVERT ( INT , SUBSTRING ( @@VERSION , CHARINDEX ( '-' , @@VERSION ) + 1 , CHARINDEX ( '.' , @@VERSION ) - CHARINDEX ( '-' , @@VERSION ) - 1 ))

               

  -- 只有 SQL SERVER 2005 及以后的版本在设计表的时候才能给字段添加说明

  IF @Ver >= 9

    BEGIN

      SELECT TABLENAME =SCHEMA_NAME (c .uid )+ '.' + OBJECT_NAME (B .MAJOR_ID ),

             FIELDNAME = A . NAME ,

             B . VALUE

      INTO   #CC

      FROM   SYS.COLUMNS A ,

             SYS.EXTENDED_PROPERTIES B ,

             SYSOBJECTS C

      WHERE A . OBJECT_ID = B .MAJOR_ID

             AND A .COLUMN_ID = MINOR_ID

             AND a . object_id =c .id

                              

      UPDATE C

      SET    C .FDESCRIPTION = CONVERT ( NVARCHAR (100 ),A . VALUE )

      FROM   #CC A ,

             dbo .TableDescription B ,

             dbo .FieldDescription C

      WHERE A .TABLENAME = B .TABLENAME

             AND B .TABLEID = C .TABLEID

             AND A .FIELDNAME = C .FIELDNAME

                              

    END

   

  IF @@ERROR <> 0

    ROLLBACK TRAN

  ELSE

    COMMIT TRAN

   

  SET NOCOUNT OFF

 

GO

 

 

 

执行存储过程：

sp_UpdateDD 时更新上述两表的相关信息。

可以在 [ 选项 ] 的 [ 键盘 ] 里面设置快捷方式 Ctrl+4

 

 如果是SQL SERVER 2005可以直接将注释写在字段的说明里面。

 

查看表信息时执行存储过程：
              sp_HelpTable 参数：@TableName
               可以在[选项]的[键盘]里面设置快捷方式 Ctrl +5

代码如下：

USE master

GO

 

IF EXISTS( SELECT * FROM sysobjects WHERE NAME = 'sp_HelpTable' AND type = 'P' AND SCHEMA_NAME (uid )= 'dbo' )

  DROP PROC dbo .sp_HelpTable

GO

 

 

/*************************

PROC:sp_HelpTable   

CREATE BY:Mark Yao  

CREATE Date:2008/1/19

USE: 更新資料字典   

INPUT:@TableName 表名

OUTPUT:

Table List:

   [TableDescription] ：表描述

   [dbo.FieldDescription] ：字段描述

Eg:Exec sp_HelpTable 'TableDescription'  

   Exec sp_HelpTable 'dbo.FieldDescription'  

*************************/

CREATE PROC dbo .sp_HelpTable (

           @TableName NVARCHAR (50 ))

AS

  BEGIN

    SET NOCOUNT ON

   

    -- 定义表的拥有者、表名、分隔符的位置

    DECLARE @tbOwner NVARCHAR (50 ),@tbName NVARCHAR (50 ),@Index INT

    SELECT @Index = CHARINDEX ( '.' ,@TableName )

    SELECT @tbOwner = CASE @Index

       WHEN 0 THEN ( SELECT TOP 1 SCHEMA_NAME (uid ) FROM Sysobjects

                      WHERE name =@TableName and Type = 'U' )

         ELSE Left(@TableName ,@Index -1 )

       End

       ,@tbName = CASE @Index

       WHEN 0 THEN @TableName

         ELSE Right(@TableName , len (@TableName )-@Index )

       END

 

 

  -- 查看表信息

    SELECT *

    FROM   dbo .TableDescription

    WHERE TABLENAME = @tbOwner + '.' +@tbName

                    

  -- 查看字段信息    

    SELECT   A .*,

             B .FDescription ,

             B .FDescription_en

    FROM     ( SELECT TableName = CASE

                                   WHEN A .COLORDER = 1 THEN D . NAME

                                   ELSE ''

                                 END ,

                     FieldSN = A .COLORDER ,

                     FieldName = A . NAME ,

                     IsIdentity = CASE

                                    WHEN COLUMNPROPERTY (A .ID ,A . NAME , 'IsIdentity' ) = 1 THEN '√'

                                    ELSE ''

                                  END ,

                     PrimaryKey = CASE

                                    WHEN EXISTS ( SELECT 1

                                                 FROM   SYSOBJECTS

                                                 WHERE XTYPE = 'PK'

                                                        AND PARENT_OBJ = A .ID

                                                        AND NAME IN ( SELECT NAME

                                                                     FROM   SYSINDEXES

                                                                     WHERE INDID IN ( SELECT INDID

                                                                                      FROM   SYSINDEXKEYS

                                                                                      WHERE ID = A .ID

                                                                                             AND COLID = A .COLID ))) THEN '√'

                                    ELSE ''

                                  END ,

                     Type = B . NAME ,

                     Prec = COLUMNPROPERTY (A .ID ,A . NAME , 'PRECISION' ),

                     Scale = ISNULL ( COLUMNPROPERTY (A .ID ,A . NAME , 'Scale' ),0 ),

                     [IsNull] = CASE

                                  WHEN A .ISNULLABLE = 1 THEN '√'

                                  ELSE ''

                                END ,

                     [Default] = ISNULL (E . TEXT , '' )

              FROM   SYSCOLUMNS A

                     LEFT JOIN SYSTYPES B

                       ON A .XUSERTYPE = B .XUSERTYPE

                     INNER JOIN SYSOBJECTS D

                       ON A .ID = D .ID

                          AND D .XTYPE = 'U'

                          AND D . NAME <> 'dtproperties'

                     LEFT JOIN SYSCOMMENTS E

                       ON A .CDEFAULT = E .ID

              WHERE D . NAME = @tbName AND SCHEMA_NAME (d .uid )=@tbOwner ) A ,

             ( SELECT B .FieldName ,

                     B .FDESCRIPTION ,

                     B .FDESCRIPTION_EN

              FROM   dbo .TableDescription A ,

                     dbo .FieldDescription B

              WHERE   A .TABLENAME =@tbOwner + '.' +@tbName AND

                      A .TABLEID = B .TABLEID ) B

    WHERE    A .FieldName = B .FieldName

    ORDER BY A .FieldSN

            

    SET NOCOUNT OFF

   

  END

 

GO 

 

这样我们以后如果只需要在表清单（TableDescription）里面添加或修改中文说明就可以了。

 

因时间关系,这次就写到这里,欢迎大家讨论.