数据字典：此文指表的中文意思以及字段的中文含义。
另：数据字典的结构有参考金蝶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