Recently I had a task to document a fairly large database (around 500 objects) and details I found here really helped.
Here is some feedback on how we implemented this – hopefully someone will find it useful even though it’s quite late.
Technique:
Extended properties in all objects + third party tool to generate docs
What was documented:
All tables and some columns (we added good descriptions for all tables to really make sure it’s clear what the table is about)
All views – descriptions on why the view was created, what tables are included in the data and such and also when to use
All stored procedures – while going through the process we found that we had a lot of duplicate stored procedures ( devs didn’t bother to see if proc exists so they created new ones )
All UDFs and some other objects as well but not all (we didn’t really have the need to document triggers)
What we ended up doing is having our DBA to reject all DDL updates that come from devs unless there are extended properties.
We also have a scheduled task to automatically re-create documentation every 2 weeks.
I use this tool (open source): http://www.codeplex.com/datadictionary. All the information I create gets added in the Extended Properties of the database.
We generate the database's dictionnary on the developper's side of the application. We have a nice procedure using an ADODB connection + ADOX objects and collections. This procedure will browse all tables in the database. The following main data is collected:
TableName
ColumnName
ColumnType
ColumnSize
bool_ColumnIsThePrimaryKey
bool_ColumnHasReferentialIntegrityConstraint
You can also follow up field default values, etc.
It is then possible for example to :
check in how many tables my field currency_id (primary key of the Tbl_currency table)is referenced, and if referential integrity is each time properly implemented (we very often create the field without implementing the corresponding rules ...).
Make sure that fields of similar logical type (like "description" fields) are of similar data type/size. Nothing is so frustrating that having a item_Description nvarchar(50) field in a table, and a document_Description ntext in another table!
etc.
All the data extracted through the procedure is inserted in a local table (could be an XML file or whatever) and saved for further use.
A column dictionnary/report can be generated from this data through
Personally I prefer to create the Data Dictionary while the database is being designed. Of course, this isn't always an option...
I think the answer depends on the current state of the database? Is it done and in production? Have you yet to start on it? (etc.)
In the past, like Cade Roux, I've pulled the information from INFORMATION_SCHEMA in to an access database. Currently we have developers occasionally adding information about the different tables, columns, stored procedures, functions, etc. to the Access database. Inside the Access database, we've created reports to output a neat looking "Data Dictionary" print out.
It is not the most efficient way to accomplish creating a data dictionary but given that the project had gone 3 years without any sign of a data dictionary, this is what we had to do.
So ultimately, the answer to this depends on your database's state.
We wrote our own Data dictionary utility that used extended properties, but when we found Redgate's tool, we abandoned it for their tool. Worked great for us! I guess it helped that we already had field and table descriptions out there in extended properties. Not to advertise for a company, but they have a 14day free trial. It's worth a look. http://www.red-gate.com/products/SQL_Doc/index.htm
发布评论
评论（9）
最近，我有一项任务是记录一个相当大的数据库（大约 500 个对象），我在这里发现的详细信息确实很有帮助。
以下是关于我们如何实现这一点的一些反馈 - 希望有人会发现它有用，即使已经很晚了。
技术：
记录的内容：
所有表格和一些列（我们为所有表格添加了良好的描述，以真正确保表格的内容清晰）
所有视图 - 关于创建视图的原因、数据中包含哪些表格等以及何时使用的描述
所有存储过程 - 在完成该过程时，我们发现我们有很多重复的存储过程（ devs没有费心去查看 proc 是否存在，所以他们创建了新的）
所有 UDF 和一些其他对象，但不是全部（我们实际上不需要记录触发器）
我们最终所做的是让 DBA 拒绝来自开发人员的所有 DDL 更新，除非有扩展属性。
我们还有一项计划任务，每两周自动重新创建文档。
Recently I had a task to document a fairly large database (around 500 objects) and details I found here really helped.
Here is some feedback on how we implemented this – hopefully someone will find it useful even though it’s quite late.
Technique:
What was documented:
All tables and some columns (we added good descriptions for all tables to really make sure it’s clear what the table is about)
All views – descriptions on why the view was created, what tables are included in the data and such and also when to use
All stored procedures – while going through the process we found that we had a lot of duplicate stored procedures ( devs didn’t bother to see if proc exists so they created new ones )
All UDFs and some other objects as well but not all (we didn’t really have the need to document triggers)
What we ended up doing is having our DBA to reject all DDL updates that come from devs unless there are extended properties.
We also have a scheduled task to automatically re-create documentation every 2 weeks.
我们使用扩展属性。
要阅读它们，我们使用 sys.extended_properties
它让事情变得容易多了。
我们还使用 Red Gate SQL Doc
We use extended properties.
To read them, we use sys.extended_properties
It makes things a lot easier.
We also use Red Gate SQL Doc
SchemaSpy 是一个非常好的工具，可以对数据库的描述进行逆向工程。 这包括：
SchemaSpy is a really nice tool that can reverse engineer a description of a database. This includes:
我使用这个工具（开源）：http://www.codeplex.com/datadictionary。
我创建的所有信息都会添加到数据库的扩展属性中。
I use this tool (open source): http://www.codeplex.com/datadictionary.
All the information I create gets added in the Extended Properties of the database.
我从
INFORMATION_SCHEMA视图以及其他特定于应用程序的元数据表生成它。我还使用
INFORMATION_SCHEMA.ROUTINES的通配符搜索来跟踪代码中的使用模式并识别未使用的列和表。这篇文章刚刚出现在我的新闻提要之一中：http://www.mssqltips。 com/tip.asp?tip=1619
I generate it from
INFORMATION_SCHEMAviews plus other tables of metadata which are application specific.I also use wildcard searches of
INFORMATION_SCHEMA.ROUTINESin order to track usage patterns within code and identify unused columns and tables.This article just showed up in one of my news feeds: http://www.mssqltips.com/tip.asp?tip=1619
我们在应用程序的开发人员一侧生成数据库的字典。 我们有一个很好的过程，使用 ADODB 连接 + ADOX 对象和集合。 此过程将浏览数据库中的所有表。 收集以下主要数据：
您还可以跟踪字段默认值等。
然后可以例如：
currency_id（主键Tbl_currency表）被引用，如果引用完整性是每个
时间正确实施（我们非常
经常创建字段而无需
执行相应规则
...）。
逻辑类型（如“
描述”字段）具有相似的数据
类型/尺寸。 没有什么比这更令人沮丧的了
表中有一个
item_Description nvarchar(50)字段，以及document_Description ntext在另一个桌子！
将插入本地表（可以是 XML 文件或其他文件）并保存以供进一步使用。
可以通过此数据生成列字典/报告
We generate the database's dictionnary on the developper's side of the application. We have a nice procedure using an ADODB connection + ADOX objects and collections. This procedure will browse all tables in the database. The following main data is collected:
You can also follow up field default values, etc.
It is then possible for example to :
currency_id(primary key of theTbl_currencytable)is referenced,and if referential integrity is each
time properly implemented (we very
often create the field without
implementing the corresponding rules
...).
logical type (like "
description"fields) are of similar data
type/size. Nothing is so frustrating
that having a
item_Description nvarchar(50)field in a table, and adocument_Description ntextin anothertable!
All the data extracted through the procedure is inserted in a local table (could be an XML file or whatever) and saved for further use.
A column dictionnary/report can be generated from this data through
就我个人而言，我更喜欢在设计数据库时创建数据字典。 当然，这并不总是一个选择......
我认为答案取决于数据库的当前状态？ 已经完成并投入生产了吗？ 你还没有开始做吗？ （等等）
过去，像 Cade Roux 一样，我将信息从 INFORMATION_SCHEMA 提取到访问数据库中。 目前，我们有开发人员偶尔向 Access 数据库添加有关不同表、列、存储过程、函数等的信息。 在 Access 数据库内，我们创建了报告来输出外观整洁的“数据字典”打印输出。
这不是完成创建数据字典的最有效方法，但考虑到该项目已经过去了 3 年，没有任何数据字典的迹象，这就是我们必须要做的。
所以最终，这个问题的答案取决于数据库的状态。
最好的问候，
坦率
Personally I prefer to create the Data Dictionary while the database is being designed. Of course, this isn't always an option...
I think the answer depends on the current state of the database? Is it done and in production? Have you yet to start on it? (etc.)
In the past, like Cade Roux, I've pulled the information from INFORMATION_SCHEMA in to an access database. Currently we have developers occasionally adding information about the different tables, columns, stored procedures, functions, etc. to the Access database. Inside the Access database, we've created reports to output a neat looking "Data Dictionary" print out.
It is not the most efficient way to accomplish creating a data dictionary but given that the project had gone 3 years without any sign of a data dictionary, this is what we had to do.
So ultimately, the answer to this depends on your database's state.
Best Regards,
Frank
我们编写了自己的使用扩展属性的数据字典实用程序，但是当我们发现 Redgate 的工具时，我们放弃了它而使用了他们的工具。 对我们来说非常有用！ 我想这有助于我们在扩展属性中已经有了字段和表描述。 不是为某家公司做广告，但他们有 14 天的免费试用期。 值得一看。 http://www.red-gate.com/products/SQL_Doc/index.html嗯
We wrote our own Data dictionary utility that used extended properties, but when we found Redgate's tool, we abandoned it for their tool. Worked great for us! I guess it helped that we already had field and table descriptions out there in extended properties. Not to advertise for a company, but they have a 14day free trial. It's worth a look. http://www.red-gate.com/products/SQL_Doc/index.htm
我很幸运地使用了 SQL 数据字典。
I've had good luck with SQL Data Dictionary.