Solved

export data dictionary SQL 2005

Posted on 2011-02-12
8
605 Views
Last Modified: 2012-05-11
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.
0
Comment
Question by:GTAJR
  • 3
  • 3
  • 2
8 Comments
 
LVL 23

Expert Comment

by:wdosanjos
ID: 34879986
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.
0
 
LVL 17

Expert Comment

by:dbaSQL
ID: 34880369
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
0
 

Author Comment

by:GTAJR
ID: 34882751
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.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 17

Expert Comment

by:dbaSQL
ID: 34882908
because Visio is a graphical editor, the output is totally customizable.  You can edit it to say whatever you like.
0
 

Author Comment

by:GTAJR
ID: 34883014
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?
0
 
LVL 23

Accepted Solution

by:
wdosanjos earned 125 total points
ID: 34883267
Please check the following view.  It should be able to provide what you are looking for:

select * from INFORMATION_SCHEMA.COLUMNS
ORDER BY TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION

More about Information Schema Views on the link below:

http://msdn.microsoft.com/en-us/library/ms186778%28v=SQL.90%29.aspx

I hope this helps.
0
 
LVL 17

Assisted Solution

by:dbaSQL
dbaSQL earned 125 total points
ID: 34884810
The first suggestion I gave you was Database Diagrams within SSMS.  See the attached, I just chose 'New Database Diagram' within the hierarchy of AdventureWorks datbase.  I picked a couple of tables that I knew were related, and just hit the add button.  By default, I just got the three tables, in the format that you see in SalesTerritory(Sales) and Customer(Sales), which is just the table name, the primary key is indicated with a little key picture, and the relationships are represented with the little reference key, linking the tables.

Then, I just went up into the 'Table View' menu item.  You will see Standard, Column Names, Keys, Name Only, Custom, and Modify Custom.  I had the SalesTerritoryHistory(Sales) table selected, or highlighted at that time, and I chose 'Modify Custom'.  That is the dialog box you see with 'Column Selection' in the header.  Here, you choose the type of output you want.  I chose three, and you can see the format of that table is slightly different now, in that it contains the datatypes, and whether or not it is nullable.

There was no typing in any of that.  I just selected some tables, and modified the output format that the diagram is produced in, within the Database Diagram options.

I know the Visio add-in actually has similar defaults, used to extract the database object definition from SQL Server.  Unfortunately, I don't have one in front of my right now, to give you a snapshot.


Database Diagram example
0
 

Author Closing Comment

by:GTAJR
ID: 34908360
both options were very good.  thank you for helping.
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Query 2 63
Analysis of table use 7 54
Sql query 107 74
MS SQL Server - Looking to filter rows based on column value 3 48
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question