Link to home
Start Free TrialLog in
Avatar of GTAJR
GTAJR

asked on

export data dictionary SQL 2005

Experts- I am looking for a way to export a view of all of our tables, columns, and relationships (data dictionary) to excel or visual format. I have a DB called "Test" that is fairly big.  240+ tables.  The developer never created a formal DDL, and I am looking to create one, and be able to print if needed.

If there is a cut and paste script that i can append when i click "new query", that would be great.  If there is any known good freeware, that is great also.  

I attempted to use some freeware and it created 240 individual scripts.  That will not do.  If there is one script to extract the entire schema that would be best.
Avatar of wdosanjos
wdosanjos
Flag of United States of America image

On SQL Server Management Studio, select your database, right click, and select 'Tasks / Generate Scripts...' from the menu.  On the pop-up window, select 'Script entire database', and press the Next button through all the screens.  This generates a script to create all your the database objects.

This should provide what you need. Please let me know otherwise.
For the 'data dictionary', I suggest trying out the 'Database Diagrams' in SSMS, at yourserver\databases\yourdatabase\Database Diagram

Right click this folder, and choose 'New Database Diagram'.

There are many different ways you can handle it, but in the designer you will add the tables that you want to include.  If a relationship exists, it will be automatically reflected within the diagram.  And you can always edit what SQL gives back to you.... all tables, some tables, just check it out.


And, of course, there is always Visio (which i love).  It has some very nice integration between SQL, that will allow you to extract the ERD (data dictionary) from your database.
http://msdn.microsoft.com/en-us/library/bb267248.aspx
Avatar of GTAJR
GTAJR

ASKER

Will the Visio option also have the definition of the table?  For example, if the real table is something like [database].[dbo].[xyz_employee]   - would Visio list the table as i just listed, then next to it it would say Employee so a sales person can look at this with a client and show a much friendlier version.
because Visio is a graphical editor, the output is totally customizable.  You can edit it to say whatever you like.
Avatar of GTAJR

ASKER

ok thanks. the issue is that i have over 250 tables.  seems like a lot of work and typing. is there a way to automatically have the definition come over with the name of the table without manually typing?  maybe exporting to excel maybe a bit easier to accomplish with what i am looking to do?
ASKER CERTIFIED SOLUTION
Avatar of wdosanjos
wdosanjos
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of GTAJR

ASKER

both options were very good.  thank you for helping.