How to store a SQL Server database diagram into a file and share it with others?

Sometimes you might feel you need to store the database diagrams in a file basis. I’ve faced to this feeling specially when I was testing some databases which were under development. I didn’t like to create the whole database diagrams that I’ve created before in a database that a new version of it is newly released for testing as it is a kind of rework and it is a time consuming process. So I decided to store the database diagrams in a file system basis that I can reuse it again and again. The following solution could be great for sharing the database diagrams in the development team as well by sending them the diagram file by email and/or storing the database diagram file in a network shared folder. Here is a work around that might help you. First of all you should install diagram support in your database. To do so just right click on the database diagrams in object explorer in SQL Server Management Studio and select “Install Diagram Support”.

image

Now you are able to create your diagrams.

Where the database diagrams are stored?

Actually the diagrams are stored in a table called “sysdiagrams”. The sysdiagrams contains a column named “definition”. The structure of the diagram is stored in the “definition” column using varbinary data type. As it is obvious it is a system table and you can find it in “System Tables” folder in SQL Server Management Studio’s object explorer. In sysdiagrams, “diagram_id” is an identity primary key so it will be generated automatically.

Getting Started

We know where the database diagrams are stored now. But, the database diagrams are stored in a varbinary column in a table. The first question here is how do we get the data out of the table? OR how do we export the database diagrams? There are two approaches here:

  • Storing the database diagrams’ data out of the table into a text file manually
  • Storing the database diagram’s data into a text by writing some T-SQL scripts

    The advantage of the first approach is that it is easier to work with, however, we should redo all the process whenever we want to share the database diagram with others. In contrast, the second approach is more complicated to implement, but, we can reuse it as much as we want with the minimum of time.

  • Storing the database diagrams’ data out of the table manually

    1.  Run the following query

    select * from sysdiagrams where name=‘DBDiagram’

    2.  In the results, highlight the definition column

    3.  Right click-> Save Results As…-> store the result in a text file

    4.  Open the text file and copy the last part of its content (the longest part)

    image

    5.  Write the following query:

    insert into sysdiagrams

               (name,principal_id,version,definition)

               values (‘DBDiagram’,1,1,paste the copied part of the text file here)

          6.  Send the query above to the team and as soon as they run the query they will have exactly the same database diagram in their database. Note that they should install diagram support first.

    All done.

    • Storing the database diagram’s data into a file using T-SQL

    We want to store the data out of the table in a file programmatically. So we need to use bcp utility and xp_cmdshell stored procedure. The bcp (Bulk Copy Programme) utility bulk copies data between an instance of Microsoft SQL Server and a data file in a user-specified format. The xp_cmdshell is a system stored procedure that executes a given command string as an OS command shell and return any outputs as rows of text. From SQL Server 2005 xp_cmdshell is a server option that enables system admins to control the ability of executing xp_cmdshell on a system. So, for security reasons this option is disabled by default and SQL Server will block xp_cmdshell. So, we need to enable it by the following codes otherwise we will face to the “SQL Server blocked access to procedure ‘sys.xp_cmdshell’ of component ‘xp_cmdshell’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘xp_cmdshell’ by using sp_configure. For more information about enabling ‘xp_cmdshell’, see “Surface Area Configuration” in SQL Server Books Online.” error in the next steps.

      • Enabling xp_cmdshell option in SQL Server 2012:

    — To allow advanced options to be changed.

    EXEC sp_configure ‘show advanced options’, 1

    — To update the currently configured value for advanced options.

    RECONFIGURE

    — To enable the feature.

    EXEC sp_configure ‘xp_cmdshell’, 1

    — To update the currently configured value for this feature.

    RECONFIGURE

     

    OR we can enable this option from SSMS (SQL Server Management Studio):

    1. From object explorer right click on the server
    2. Click “Facets”
    3. From Facets drop down list select “Surface Area Configuration”
    4. Change the value of “XPCmdShellEnabled” to “True” and OK.

    image

    Now we are going to store the database diagram into a file.

      • Storing the content of a database diagram in a file using T-SQL:

    DECLARE @sql varchar(8000) 

    SET @sql=‘BCP “select definition from [‘+db_name()+‘].dbo.sysdiagrams where name = ”DB_DIAGRAM_NAME”” queryout c:\TARGET_FOLDER\Diagram -c -t, -T -S’+@@SERVERNAME

    exec xp_cmdshell @sql

     

    Important Notes: You need to be aware of some important considerations about the code above:

    1. The output of the query will create the file in local machine that hosts the SQL Server database
    2. The service account for the SQL Server should have write permissions to “TARGET_FOLDER”
    3. If you want to save the file in a networked shared folder you need to change the target folder’s path from “c:\TARGET_FOLDER\Diagram” to “\\COMPUTER_NAME\C$\TARGET_FOLDER\Diagram” and again the service account of the SQL Server should have write permissions to the network shared folder. If SQL Server is running under a local service account you might need to define a proxy account and grant access rights to it.
    4. The output file will be over written whenever you run the query

    After running the above query a file will be created in the target folder containing the database diagram’s content in binary.

  • Create a database diagram from file
  • Now that we’ve stored the content of the diagram in a file format, we need to create the diagram in the other computers. Obviously the other computers’ SQL Server service accounts should have read access rights to the “TARGET_FOLDER” that stored in the “\\COMPUTER_NAME\C$\” path. By running the following code you can create the exact copy of the original diagram in the other machines:

    createtable #tbl (def varbinary(max))

    bulkinsert #tbl from ‘C:\TARGET_FOLDER\Diagram

    insert into sysdiagrams

                  (name,principal_id,version,definition)

                  values (‘DB_DIAGRAM_NAME’,1,1,(select def from #tbl))

    drop table #tbl

     

    NOTE: If you are just testing the whole solution in a single computer the above code will work perfectly, but,  if you want to read the file from a network shared folder then the “bulk_insert” command should point to the network shared folder path like “\\COMPUTER_NAME\C$\TARGET_FOLDER\Diagram”. So the second line of the above code should be something like this:

    bulk insert #tbl from ‘\\COMPUTER_NAME\C$\TARGET_FOLDER\Diagram

    All done!

5 thoughts on “How to store a SQL Server database diagram into a file and share it with others?

  1. This doesn’t seem to work any more – When trying to open the diagram I get:
    ===================================

    The docfile has been corrupted.
    (MS Visual Database Tools)

    ——————————
    Program Location:

    at Microsoft.SqlServer.Management.DataTools.Interop.IDTDatabaseDiagramFactory.EditDBDiagram(Object dsRef, Object pServiceProvider)
    at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.DatabaseDesignerNode.CreateDesigner(IDTDocToolFactoryProvider factoryProvider, IVsDataConnection dataConnection)
    at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VsDataDesignerNode.CreateDesigner()
    at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VsDataDesignerNode.Open()
    at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VirtualProject.CreateDesigner(Urn origUrn, DocumentType editorType, DocumentOptions aeOptions, IManagedConnection con, String fileName)
    at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VirtualProject.Microsoft.SqlServer.Management.UI.VSIntegration.Editors.ISqlVirtualProject.CreateDesigner(Urn origUrn, DocumentType editorType, DocumentOptions aeOptions, IManagedConnection con, String fileName)
    at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.ISqlVirtualProject.CreateDesigner(Urn origUrn, DocumentType editorType, DocumentOptions aeOptions, IManagedConnection con, String fileName)
    at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VsDocumentMenuItem.CreateDesignerWindow(IManagedConnection mc, DocumentOptions options)

    1. Hi there,

      Welcome to BIInsight.com.
      I just tested on my test environment and everything works fine.
      Can you please explain more about the issue that you faced?
      I explained two different methods of exporting/importing SQL Server database diagrams. In which method you got the error message?
      If you used the copy/paste method, make sure you don’t put the text file contents in quotation marks. You also need to only copy the part explained in the article.
      Please update me with the outcome.

      Cheers.

  2. The following code fail similarly for me:
    insert into sysdiagrams
    (name,principal_id,version,definition)
    values (‘DBDiagram’,1,1,paste the copied part of the text file here)

    However, this does work:
    DECLARE @OriginalName nvarchar(128)
    DECLARE @NewName nvarchar(128)

    SET @OriginalName = ‘MyDB’
    SET @NewName = @OriginalName + ‘Copy’

    INSERT INTO sysdiagrams
    (name,principal_id,version,definition)
    VALUES (@NewName,1,1,
    (SELECT definition FROM sysdiagrams WHERE [name]=@OriginalName))

    So, it would seem that there is something failing when copying out of the results pane back into the SQL pane. I have tried both cast and convert with the binary data, but not had any success.

  3. OK, I figured out what was going on. The results pane truncates long data. And binary data is long. 🙂

    Executing this code will force the return of all the data.
    SELECT name,
    principal_id,
    diagram_id,
    [version],
    cast(convert(varchar(max), [definition], 1) as xml) AS [definition]
    FROM sysdiagrams WHERE name=’DBDiagram’

    When you execute this, all the data for the definition column will be returned to the results pane no matter how long.

Leave a Reply

Your email address will not be published. Required fields are marked *


The reCAPTCHA verification period has expired. Please reload the page.