数据字典:此文指表的中文意思以及字段的中文含义。
另:数据字典的结构有参考金蝶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
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)里面添加或修改中文说明就可以了。
因时间关系,这次就写到这里,欢迎大家讨论.