September 14, 2008 at 6:27 pm
I have migrated a few databases from MsAccess to SQL Server 2005 and would like to make a data dictionary available to help with code writing and documentation of the databases. I have not found a stable tool so far.
I tried "Data Dictionary Creator" but it only works on tables.
Has anyone used any recommended products that could help me?
Thank you.
MGA
September 15, 2008 at 12:40 am
I've not personally used it, but I have heard good rumblings about the SQL Doc tool that you get from Red-Gate.
September 15, 2008 at 6:09 am
Ghandi,
From what I tested, SQL does not allow you to document the db down to the fields level. It is a great tool if you do not need to document your database down to the fields level.
Thank you.
MGA
September 15, 2008 at 8:16 am
Most of these tools assemble data from your tables into a format you can use. Unless you have extended properties on every fiekd, there isn't data that you can use to document things.
Best bet is use one of these tools to build you a document and then you mark it up to show the meaning of the fields.
September 15, 2008 at 8:08 pm
What I need will be a tool similar to Data Dictionary creator that allows you to add description to your database:
Descrition of what a given table is used for and the description of the fields contained in tables.
In SQL Server 2005, virtually each database object has extended properties on it and if the information is not populated, then there is nothing to report on. I could manually add information in the extended properties of each object using SQL Server Mgmt Studio but it will take a long time.
The tool I am looking for will allow me to quickly key in the description in a reasonable amount of time.
Thank you.
MGA
September 16, 2008 at 6:09 am
Check BOL under "Extended Properties [SQL Server]. I have no idea why it takes SSMS so long to add an extended property to a column, but you can do it in a query and it runs fast. Try adding your descriptions to a table of table names and column names, and process it via a query.
Here is an example:
EXEC sys.sp_addextendedproperty
N'Description'
, 'Database update date'
, N'SCHEMA',DBO
, N'TABLE',[Current PP]
, N'COLUMN',Date
As I recall, you have to either add a new extended property, or update an existing one. There is a set of sp_xxxextendedproperty system stored procedures.
September 16, 2008 at 10:06 am
You can get a lot of information out of the information_schema views. See BOL:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/7e9f1dfe-27e9-40e7-8fc7-bfc5cae6be10.htm
If you join the views in a clever manner, you can show each column as well as constraints.
September 16, 2008 at 11:56 am
Guy (9/15/2008)
Ghandi,From what I tested, SQL does not allow you to document the db down to the fields level. It is a great tool if you do not need to document your database down to the fields level.
Thank you.
MGA
Guy,
I use SQLDoc by Redgate and guess what.., you CAN document your database to the field level !!
You can already via SSMS document via the standard "description" field but i find that a little bit slow..
Or you can use SqlDoc for generating the documentation for you:
@t the point that you have selected the objects you can then from SqlDoc directly write the notes at the field level, and from here you can create also a hard copy (html documentation)
I find it verry usefull,
The one thing i miss is, a summary of every fieldname & tablename so that no mistakes happen like
ex: "Facturen" and "Fakturen" , notice the little change, can be annoying though..
wkr,
Eddy
September 16, 2008 at 12:07 pm
The whole Meta-Data process in SQL 2005 seems a bit over kill. I as a developer can appreciate the entent of control & options that SQL Server 2005 enables us via the Extended Proeprties such as being able to generate multipl meta-data tags or descriptions to the same item insetad of having a single notes or comments field for an object that must contain all meta data. However it seems like they should have also added the ability to generate & work with meta data in a much esaier manner.
For example you should be able to simply tell SQL Server to add 'X' test to the default extended property to an object so that if you aren't looking for named meta data you don't have to keep track of what name was used and or worry about ensuring every Extended Proeprty of an Object uses the same name.
Lastly, of all the things they did not add to SQL Server 2005 that they should have in my opinion is a basic Data Dictionary tool. It's true that one could failry easily do this by generating their own custom code via UDF or SP but by having this as part of the core product, you promote consistency in it's use.
That's my 2 cents.
Kindest Regards,
Just say No to Facebook!Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply