数据字典：此文指表的中文意思以及字段的中文含义。
另：数据字典的结构有参考金蝶K3的表结构，而脚本则为自己编写。
我们在设计后台数据库时，一般表名和字段名是英文 。且一般每个人都只对自己所属模块熟，而在需要用到其他模块的表时，则需要询问其他人。这样比较麻烦，且如果当初设计此表的人已经离开，则还需看代码，这样就更不方便了。因此对于一个好的数据库来说设计数据字典则非常必要。
设计数据字典要有两个表，一个是表清单（TableDescription），一个是字段表（FieldDescription）。
表清单用来存放所有的非系统表，而字段表用来存放所有的字段以及数据类型及中文含义。
设计这两个表的脚本如下：
/******
对象
: Table [dbo].[TableDescription] ******/
CREATE
TABLE [dbo]
.[TableDescription]
(
IDENTITY
(1
,1
)
NOT
FOR
REPLICATION
NOT
NULL,
(50
)
NOT
NULL,
--
表名
(100
)
NULL,
--
中文说明
(200
)
NULL,
--
英文说明
CONSTRAINT [PK_TableDescription_TableID]
PRIMARY
KEY
CLUSTERED
(
ASC
)
WITH
(PAD_INDEX
=
OFF
, STATISTICS_NORECOMPUTE
=
OFF
, IGNORE_DUP_KEY
=
OFF
, ALLOW_ROW_LOCKS
=
ON
, ALLOW_PAGE_LOCKS
=
ON
)
ON
)
ON
--
添加维一约束
ALTER
TABLE TableDescription
ADD
CONSTRAINT un_TableDescription_TableName
UNIQUE
(TableName
)
/******
对象
: Table [dbo].[FieldDescription] ******/
CREATE
TABLE [dbo]
.[FieldDescription]
(
IDENTITY
(1
,1
)
NOT
FOR
REPLICATION
NOT
NULL,
INT
NOT
NULL,
(50
)
NOT
NULL
,
--
字段名称
(20
)
NOT
NULL,
--
字段类型
NULL,
--
长度
NULL,
--
小数位数
(100
)
NULL,
--
中文说明
(200
)
NULL,
--
英文说明
CONSTRAINT [PK_t_TableDescription]
PRIMARY
KEY
NONCLUSTERED
(
ASC
,
ASC
)
WITH
(PAD_INDEX
=
OFF
, STATISTICS_NORECOMPUTE
=
OFF
, IGNORE_DUP_KEY
=
OFF
, ALLOW_ROW_LOCKS
=
ON
, ALLOW_PAGE_LOCKS
=
ON
)
ON
)
ON
--
添加外键约束
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
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
NVARCHAR
(100
),
--
表名
INT
,
--
字段數
INT
--SQL SERVER
的版本号
BEGIN
TRAN
--1.1
刪除已經不存在的表
DELETE dbo
.TableDescription
WHERE TABLENAME
NOT
IN
(
SELECT SCHEMA_NAME
(uid
)+
'.'
+
NAME
FROM
WHERE
TYPE
=
'U'
)
--1.2
將新增的表插入到：
TableDescription
INSERT
INTO dbo
.TableDescription
(TABLENAME
)
SELECT schema_name
(uid
)+
'.'
+
NAME
FROM
WHERE
TYPE
=
'u'
AND schema_name
(uid
)+
'.'
+
NAME
NOT
IN
(
SELECT
FROM dbo
.TableDescription
)
--2.
更新表結構到
FieldDescription
SELECT TABLENAME
=SCHEMA_NAME
(d
.uid
)+
'.'
+
OBJECT_NAME
(A
.ID
),
= A
.COLORDER
,
= A
.
NAME
,
= B
.
NAME
,
=
CASE B
.
NAME
WHEN
'nvarchar'
THEN A
.LENGTH
/
ELSE A
.LENGTH
END
,
=
CASE
WHEN A
.XTYPE
= 61
THEN
ELSE
ISNULL
(
COLUMNPROPERTY
(A
.ID
,A
.
NAME
,
'scale'
),0
)
END
INTO
FROM
LEFT
JOIN
ON A
.XUSERTYPE
= B
.XUSERTYPE
INNER
JOIN
ON A
.ID
= D
.ID
AND D
.XTYPE
=
'U'
AND D
.
NAME
<>
'dtproperties'
LEFT
JOIN
ON A
.CDEFAULT
= E
.ID
SELECT B
.TABLEID
,
.*
INTO
FROM #AA A
,
.TableDescription B
WHERE A
.TABLENAME
= B
.TABLENAME
--
导出已经不存在的字段清单
SELECT a
.FieldID
INTO
FROM dbo
.FieldDescription a
left
join
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
FROM #Field
)
--
新增的字段
SELECT b
.*
INTO
FROM dbo
.FieldDescription a
right
join
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
,
,
,
,
)
SELECT TABLEID
,
,
,
,
SCALE
FROM
--
获取
SQL SERVER
版本号
SELECT @Ver
=
CONVERT
(
INT
,
SUBSTRING
(
@@VERSION
,
CHARINDEX
(
'-'
,
@@VERSION
)
+ 1
,
CHARINDEX
(
'.'
,
@@VERSION
)
-
CHARINDEX
(
'-'
,
@@VERSION
)
- 1
))
--
只有
SQL SERVER 2005
及以后的版本在设计表的时候才能给字段添加说明
IF @Ver
>=
BEGIN
SELECT TABLENAME
=SCHEMA_NAME
(c
.uid
)+
'.'
+
OBJECT_NAME
(B
.MAJOR_ID
),
= A
.
NAME
,
.
VALUE
INTO
FROM
SYS.COLUMNS A
,
SYS.EXTENDED_PROPERTIES B
,
SYSOBJECTS C
WHERE A
.
OBJECT_ID
= B
.MAJOR_ID
AND A
.COLUMN_ID
=
AND a
.
object_id
=c
.id
UPDATE
SET C
.FDESCRIPTION
=
CONVERT
(
NVARCHAR
(100
),A
.
VALUE
)
FROM #CC A
,
.TableDescription B
,
.FieldDescription C
WHERE A
.TABLENAME
= B
.TABLENAME
AND B
.TABLEID
= C
.TABLEID
AND A
.FIELDNAME
= C
.FIELDNAME
END
IF
@@ERROR
<>
ROLLBACK
TRAN
ELSE
COMMIT
TRAN
SET
NOCOUNT
OFF
GO
执行存储过程：
sp_UpdateDD 时更新上述两表的相关信息。
可以在 [ 选项 ] 的 [ 键盘 ] 里面设置快捷方式 Ctrl+4
如果是SQL SERVER 2005可以直接将注释写在字段的说明里面。
查看表信息时执行存储过程： sp_HelpTable 参数：@TableName 可以在[选项]的[键盘]里面设置快捷方式 Ctrl +5
代码如下：
USE
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
(
NVARCHAR
(50
))
AS
BEGIN
SET
NOCOUNT
ON
--
定义表的拥有者、表名、分隔符的位置
DECLARE @tbOwner
NVARCHAR
(50
),@tbName
NVARCHAR
(50
),@Index
INT
SELECT @Index
=
CHARINDEX
(
'.'
,@TableName
)
SELECT @tbOwner
=
CASE
WHEN 0
THEN
(
SELECT
TOP 1 SCHEMA_NAME
(uid
)
FROM
WHERE
name
=@TableName
and
Type
=
'U'
)
ELSE
Left(@TableName
,@Index
-1
)
End
,@tbName
=
CASE
WHEN 0
THEN
ELSE
Right(@TableName
,
len
(@TableName
)-@Index
)
END
--
查看表信息
SELECT
*
FROM dbo
.TableDescription
WHERE TABLENAME
= @tbOwner
+
'.'
+@tbName
--
查看字段信息
SELECT A
.*,
.FDescription
,
.FDescription_en
FROM
(
SELECT TableName
=
CASE
WHEN A
.COLORDER
= 1
THEN D
.
NAME
ELSE
''
END
,
= A
.COLORDER
,
= A
.
NAME
,
=
CASE
WHEN
COLUMNPROPERTY
(A
.ID
,A
.
NAME
,
'IsIdentity'
)
= 1
THEN
'√'
ELSE
''
END
,
=
CASE
WHEN
EXISTS
(
SELECT
FROM
WHERE XTYPE
=
'PK'
AND PARENT_OBJ
= A
.ID
AND
NAME
IN
(
SELECT
NAME
FROM
WHERE INDID
IN
(
SELECT
FROM
WHERE ID
= A
.ID
AND COLID
= A
.COLID
)))
THEN
'√'
ELSE
''
END
,
Type
= B
.
NAME
,
=
COLUMNPROPERTY
(A
.ID
,A
.
NAME
,
'PRECISION'
),
=
ISNULL
(
COLUMNPROPERTY
(A
.ID
,A
.
NAME
,
'Scale'
),0
),
=
CASE
WHEN A
.ISNULLABLE
= 1
THEN
'√'
ELSE
''
END
,
=
ISNULL
(E
.
TEXT
,
''
)
FROM
LEFT
JOIN
ON A
.XUSERTYPE
= B
.XUSERTYPE
INNER
JOIN
ON A
.ID
= D
.ID
AND D
.XTYPE
=
'U'
AND D
.
NAME
<>
'dtproperties'
LEFT
JOIN
ON A
.CDEFAULT
= E
.ID
WHERE D
.
NAME
= @tbName
AND SCHEMA_NAME
(d
.uid
)=@tbOwner
) A
,
(
SELECT B
.FieldName
,
.FDESCRIPTION
,
.FDESCRIPTION_EN
FROM dbo
.TableDescription A
,
.FieldDescription B
WHERE A
.TABLENAME
=@tbOwner
+
'.'
+@tbName
AND
.TABLEID
= B
.TABLEID
)
WHERE A
.FieldName
= B
.FieldName
ORDER
BY A
.FieldSN
SET
NOCOUNT
OFF
END
GO
这样我们以后如果只需要在表清单（TableDescription）里面添加或修改中文说明就可以了。
因时间关系,这次就写到这里,欢迎大家讨论.
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
