浅谈我在SqlServer中维护简单数据词典的方法

前几天在园子里看了一篇清空所有表数据的博客，里面洋洋洒洒几十行代码，看的出作者是很用心写出的代码，可为啥我看的这么蛋疼？明明是很简单的操作嘛。

1，用下面语句生成清空所有表数据的sql语句：

--生成清空所有表数据的sql语句
select 'truncate table '+name from sysobjects where xtype='u'

效果如下：

2，把查询结果复制到查询分析器里，执行就ok了。

这种方法简单明了，相信任何人都能看懂，并且因为是分2步执行的，如果其中有不需要清空的配置信息，可以不执行相关语句或者在相关语句里加条件都很方便。

类似的，如果要删除数据库所有表也很简单了，如下语句：

--生成删除所有表的sql语句
select 'drop table '+name from sysobjects where xtype='u'

 

既然说到这里，我顺便分享下我维护数据词典的方式。我习惯把数据库每个表以及每个表的每个字段都写上备注，并且也要求公司的程序员都如此做。具体例子，比如写一个简单的日志记录程序：
1，首先创建表

-- 创建表[员工日志系统]开始: 
IF EXISTS (SELECT 1 FROM sysobjects WHERE xtype='u' AND name='logAdminWork') drop table logAdminWork
CREATE TABLE [logAdminWork] (
[id] integer IDENTITY (1, 1) PRIMARY KEY NOT NULL ,
[adminID] int,
[departMent] [varchar] (20),
[dt] [datetime],
[content] [text]
)

exec sp_addextendedproperty N'MS_Description',N'员工日志系统 ',N'user',N'dbo',N'table',N'logAdminWork', NULL, NULL
exec sp_addextendedproperty N'MS_Description',N'id',N'user',N'dbo',N'table',N'logAdminWork',N'column',N'id'
exec sp_addextendedproperty N'MS_Description',N'管理员id',N'user',N'dbo',N'table',N'logAdminWork',N'column',N'adminID'
exec sp_addextendedproperty N'MS_Description',N'部门',N'user',N'dbo',N'table',N'logAdminWork',N'column',N'departMent'
exec sp_addextendedproperty N'MS_Description',N'日期',N'user',N'dbo',N'table',N'logAdminWork',N'column',N'dt'
exec sp_addextendedproperty N'MS_Description',N'内容',N'user',N'dbo',N'table',N'logAdminWork',N'column',N'content'

-- 创建表[员工日志系统]结束。 
--------------------------

 

我习惯用sql语句创建，当然直接在企业管理器创建也是可以的，如下图填写列注释：

下图填写表注释：

2，如果所有的表都按照类似方式创建，那么查看数据词典就可用如下语句：

--查看数据词典
select isnull(m.value,x.colname+'**') as memo,x.colname,x.typename,x.fdlength,x.[isnull],x.colid,x.tblname 
from 
(Select o.name as tblname,c.name as colname,t.name as typename,c.length as fdlength,c.isnullable as [isnull] ,c.colid,o.id as oid 
from syscolumns as c,systypes as t,sysobjects as o 
Where o.id=c.id and o.xtype='u' and o.status>=0 and t.xtype=c.xtype 
) as x 
left join sys.extended_properties as m 
on m.major_id=x.oid and x.colid=m.minor_id 
order by x.tblname,x.colid

效果如下：

当然这样如果多个表的时候直接看内容太多，可以写一个简单的程序，把上面这个语句查出来的数据分组显示一下，就是一个很好的数据词典了。我简单写的一个程序界面如下：（代码就不分享了，数据都有了，如何让他显示出来对程序员来说是很简单的事）

 

不过在查询分析器里，一般一次也就看一个表比较方便。

--查看单个表的数据词典
declare @tblname varchar(20)
set @tblname='logAdminWork'
select isnull(m.value,x.colname+'**') as memo,x.colname,x.typename,x.fdlength,x.[isnull],x.colid,x.tblname 
from 
(Select o.name as tblname,c.name as colname,t.name as typename,c.length as fdlength,c.isnullable as [isnull] ,c.colid,o.id as oid 
from syscolumns as c,systypes as t,sysobjects as o 
Where o.id=c.id and o.xtype='u' and o.status>=0 and t.xtype=c.xtype 
and o.name=@tblname 
) as x 
left join sys.extended_properties as m 
on m.major_id=x.oid and x.colid=m.minor_id 
order by x.tblname,x.colid

这样做的好处不用多说，数据词典直接存在数据库中，更新维护都很方便。