?
Solved

Scripting out a Database Diagram

Posted on 2010-11-16
12
Medium Priority
?
343 Views
Last Modified: 2012-05-10
In sql server 2005, is it possible to script out a database diagram?
0
Comment
Question by:brettr
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 3
12 Comments
 
LVL 9

Expert Comment

by:Philippe Damerval
ID: 34150817
Hi,
Not sure what you mean by scripting out a database diagram? If you mean script out your database, yes, there are tools for that. If you mean turn the data normally stored in the dtproperties table into a set of insert/update statements, here is a link to an article about someone who did just that - although, considering that it is all binary data, you would probably be better off with a simple backup.

Here is the link:
http://www.codeproject.com/KB/database/ScriptDiagram2005.aspx

Hope this helps,

Philippe
0
 

Author Comment

by:brettr
ID: 34150842
I only mean scripting a database diagram.  I've created new tables and linked olds within the diagram.  I want that scripted out via the database diagram.
0
 
LVL 9

Expert Comment

by:Philippe Damerval
ID: 34150940
Still having trouble with the terminology.
If you want your database diagram scripted instead of just saved as data in dtproperties, then follow the link I pointed you to.
If you want your tables and relationships scripted, use other tools: either the ones that came with SQL server management studio that allow you to script objects in the database (tables, indexes and constraints etc) by right-clicking on a table or other object and choosing "script table as CREATE to..." or by using third party tools such as EMS SQL studio (my favorite) or red gate that let you script out an entire database in one command.

Let me know if I am still misunderstanding you.

Philippe
0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:brettr
ID: 34151055
I'd like to save the diagram and then load it into a copy of the same database that does not have the diagram.  This means the new tables and relations will need to be created.  Is there a way to do that without going to each table and scripting it?  After I change anything in the diagram, I have to go to each object and script them.  That is what I'm trying to avoid until the diagram is finalized.

Is there a way to do that without running all of the scripts mentioned in the link you provided?

Thanks.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 34151632
So are you asking:
Is there a way to transfer a SQL Server diagram for a database to another SQL Server database with an identical schema?  If so check this out:
http://www.mssqlcity.com/FAQ/TranMove/MoveDiag.htm
0
 
LVL 9

Expert Comment

by:Philippe Damerval
ID: 34151671
I think the following process is what you are trying to do:

1. Create new objects using a database diagram (you've done this)
2. Copy the database diagram into a copy of the current database that does not have the objects
3. Have the diagram automatically create the missing objects in the database copy.

If so, you can't do that - but you can use one of the tools I indicated to compare the two database and automatically generate a set of scripts to bring over any changes.

Philippe
0
 

Author Comment

by:brettr
ID: 34157662
@acperkins:

When I try to run just the select from the link you provide, I get:
Invalid object name 'mydatabase..dtproperties'
0
 
LVL 9

Expert Comment

by:Philippe Damerval
ID: 34158461
This message indicates that the dtproperties table is mising. If the dtproperties table doesn't exist that just means there have not been any diagrams created yet for the target database.

Philippe
0
 

Author Comment

by:brettr
ID: 34159534
There are two diagrams in the database I'm SELECTing on.
0
 
LVL 9

Expert Comment

by:Philippe Damerval
ID: 34159917
instead of mydatabase..dtproeprties try mydatabase.dbo.dtproperties, or replace "dbo" with the schema that the dtproperties table is in.
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 2000 total points
ID: 34160665
You are right.  In SQL Server 2005 the diagrams are no longer stored in dtproperties but rather in sysdiagrams.  The sysdiagrams table is created after you select "Yes" at the following message box:
"This database does not have one or more of the support objects required to use database diagramming. Do you wish to create them?"

See if this article helps:
Script SQL Server 2005 diagrams to a file
http://www.codeproject.com/KB/database/ScriptDiagram2005.aspx
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 2000 total points
ID: 34160693
>>instead of mydatabase..dtproeprties try mydatabase.dbo.dtproperties, or replace "dbo" with the schema that the dtproperties table is in. <<
dtproperties no longer exists in SQL Server 2005, that is unless the database was migrated from SQL Server 2000, in which case it could probably be safely DROPped.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…

718 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