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”.

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)

  

  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.

  

  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!