August 24, 2009 at 3:25 am
Dear All,
I am designing a database for our new project.Making the data dictionary using excel sheet.
(I have put the definitions of each table in sperate sheet.).
Can anyone suggest me a better way.?
How do you guys make Data Dictionary.?
Thanks in advance.
August 25, 2009 at 9:33 am
I call them Entity Relationship Diagrams, and I use ERStudio to do the work. You can use Visio and even the built-in SSMS Database Diagram feature. There are other 3rd-party tools as well.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 25, 2009 at 9:47 am
Use the Database Diagrammer built into Management Studio. Create a database. Expand it to see all the nodes. Right click on Database Diagrams. There wont be any tables to add. Right click on the surface of the diagram for the context sensitive menu and take it from there.
August 26, 2009 at 7:52 am
Hi,
Thanks for your replies.
But am not asking abt DB Diagram. Infact am pretty familar with that. Am talking abt Data Dictionary only. At present it's somthng like this.
1st sheet of excel file - Details of DD like who created, reviewed, modified by, modified on..etc;
2nd sheet - List of all the tables used and a breif decsription abt each table.
3rd sheet onwards the structure of each table in different sheets. (including indexes, FK details etc.)
If there are 100 tables in my DB then there will be 102 (2+100) sheets in my excel file.
I have been making this document this way since long time. was just wondering anyone else make it in a different way.
Thanks once again.
August 26, 2009 at 8:44 am
San (8/26/2009)
Hi,Thanks for your replies.
But am not asking abt DB Diagram. Infact am pretty familar with that. Am talking abt Data Dictionary only. At present it's somthng like this.
1st sheet of excel file - Details of DD like who created, reviewed, modified by, modified on..etc;
2nd sheet - List of all the tables used and a breif decsription abt each table.
3rd sheet onwards the structure of each table in different sheets. (including indexes, FK details etc.)
If there are 100 tables in my DB then there will be 102 (2+100) sheets in my excel file.
I have been making this document this way since long time. was just wondering anyone else make it in a different way.
Thanks once again.
ERStudio has very good capabilities re Data Dictionaries and a wealth of output formats. And since it is programmable you could roll your own if it didn't suit your need.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 26, 2009 at 8:46 am
That's certainly one way to do it.
I'd take a look at the documentation programs available from RedGate and ApexSQL. Might be more useful/efficient at it. Those are what I prefer to use.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 26, 2009 at 8:56 am
GSquared (8/26/2009)
That's certainly one way to do it.I'd take a look at the documentation programs available from RedGate and ApexSQL. Might be more useful/efficient at it. Those are what I prefer to use.
Very good point. I use ApexSQL's Doc if that is ALL I need (a data dictionary or other slick and useful output). But pretty much everyone should have an ERD as well and in this case you can get the data dictionary stuff 'for free' if you already have the ERD in ERStudio. 🙂
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 26, 2009 at 9:51 am
Start with a query something like this:
SELECT
TABLE_SCHEMA + '.' + TABLE_NAME AS [Table]
, COLUMN_NAME AS [Column]
, DATA_TYPE + COALESCE('('+CAST(CHARACTER_MAXIMUM_LENGTH AS varchar(4))+')','') AS [Data Type]
FROM
INFORMATION_SCHEMA.COLUMNS
ORDER BY
TABLE_NAME, ORDINAL_POSITION
You'll need to make it a bit more sophisticated if you have data types such as decimal in your table. You can put the results in Excel or in a table in Word and enter descriptions for each table and column. You'll then want to find a way of denoting primary key columns, foreign key constraints, triggers and so on. You'll find that you can include as much information as you've got the patience to add.
Let us know how you get on!
John
August 26, 2009 at 9:55 am
That works too. And if you include extended properties and query those, you can have definitions in there that are business-meaningful.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 26, 2009 at 10:00 am
I use extended properties and create an html document using this script. It works pretty well for what I use it for, but the third party tools definately do a better job.
August 26, 2009 at 11:47 am
Did the saem thing are you are doing. 285 table data warehouse with data dictionary in Excel 2007. The first worksheet is a list of all tables as hyperlinks. All subsequent worksheets (pages) are the table with the columns listed, data types and a brieft description of the table and its business purpose at the top.
A second document was built in the same manner for indexes since all tables have clustered and unique indexes.
At the bottom of each table definition is a button (hyperlink) to jump back to the main menu (first page).
It is this document that is given to developers and if ofter used in RFP's for customers.
I hope this helps!
Jim
casinc815
August 26, 2009 at 11:45 pm
Thank you "TheSQLGuru, GSquared, John Mitchell, Ken Simmons".
I was expecting such replies. Now I will look into all the options you said and will revert.
Thanks once again.
August 27, 2009 at 12:03 am
Sorry...Duplicate post because of Network issues.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply