Home > SQL Server > SQL Server 2008 R2 Database Diagrams Questions Answered

SQL Server 2008 R2 Database Diagrams Questions Answered


What are the database diagrams in SQL Server?

SQL Server Database Diagram is visual model of a database. We can use only Tables in SQL Server database diagrams. Any changes made outside Database Diagram designer will reflect automatically in the diagram and vice versa.

How did you create them?

We can create them using built-in SQL Server Database Diagram designer tool in SQL Server Management Studio. There is no Undo and Redo option, so we need to be careful when deleting columns or removing relationships

Do you know where exactly the database diagrams are stored in SQL Server 2008 R2?

Database Diagrams are stored in dbo.sysdiagrams system table in the database we are creating diagram. This table will be created automatically, when we create the first database diagram in the database.

The structure of this table is as follows

clip_image001

 

Is there any way how you copy one database diagram to another database?

Yes, we can copy the diagram by copy the data of dbo.sysdiagrams table from source database to destination database, but the tables in the diagram should exist in the destination database or else while viewing or modifying the diagram, we will get the below error and corresponding tables will be removed from the diagram

“Table(s) were removed from the diagram because privileges were removed to these table(s) or the table(s) were dropped.

clip_image001[5]

Query to copy database diagrams

  1. INSERT INTO [DESTINATIONDB].[dbo].[sysdiagrams]
  2. ([name]
  3. ,[principal_id]
  4. ,[version]
  5. ,[definition])
  6. SELECT [name]
  7. ,[principal_id]
  8. ,[version]
  9. ,[definition]
  10. FROM [SOURCEDB].[dbo].[sysdiagrams]

 

For copying to remote servers, we can use Linked Servers and use Four part name or use SSIS to transfer data

How will you insert database diagrams into your design document?

We can use “Edit -> Copy Diagram to Clipboard” or “Database Diagram – Copy Diagram to Clipboard” menu option to copy the diagram and then paste it in the document required. If the diagram is bigger, we can paste it to Paint Brush or any image editor and then select specific portions and copy one by one in document.

We can also the print the database diagram as PDF, if we have PDF Print drivers such as BioPDF or CutePDF installed.

Advertisements
  1. June 2, 2011 at 1:16 am

    Good one Arjun! You need to answer in the blogpost to the question. and tweet the answer. We will evaluate based on number of retweets and the quality of the answer. I appriciate your intrest. All the best

    Like

  2. Tara Kant
    November 23, 2011 at 12:58 am

    Dear….

    Its very help full script for sysdaigrams export to another database..

    Regards
    Tarakant

    Like

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: