Storing the database diagrams’ knowledge out of the desk manually

1.  Run the next question

choose * from sysdiagrams the place title=‘DBDiagram’

2.  Within the outcomes, spotlight the definition column

3.  Proper click-> Save Outcomes As…-> retailer the end in a textual content file

4.  Open the textual content file and duplicate the final a part of its content material (the longest half)

5.  Write the next question:

insert into sysdiagrams

           (title,principal_id,model,definition)

           values (‘DBDiagram’,1,1,paste the copied a part of the textual content file right here)

      6.  Ship the question above to the workforce and as quickly as they run the question they may have precisely the identical database diagram of their database. Notice that they need to set up diagram assist first.

All performed.

• Storing the database diagram’s knowledge right into a file utilizing T-SQL

We wish to retailer the information out of the desk in a file programmatically. So we have to use bcp utility and xp_cmdshell saved process. The bcp (Bulk Copy Programme) utility bulk copies knowledge between an occasion of Microsoft SQL Server and a knowledge file in a user-specified format. The xp_cmdshell is a system saved process that executes a given command string as an OS command shell and return any outputs as rows of textual content. From SQL Server 2005 xp_cmdshell is a server possibility that allows system admins to manage the power of executing xp_cmdshell on a system. So, for safety causes this selection is disabled by default and SQL Server will block xp_cmdshell. So, we have to allow it by the next codes in any other case we’ll face to the “SQL Server blocked entry to process ‘sys.xp_cmdshell’ of element ‘xp_cmdshell’ as a result of this element is turned off as a part of the safety configuration for this server. A system administrator can allow the usage of ‘xp_cmdshell’ through the use of sp_configure. For extra details about enabling ‘xp_cmdshell’, see “Floor Space Configuration” in SQL Server Books On-line.” error within the subsequent steps.

• Enabling xp_cmdshell possibility in SQL Server 2012:

— To permit superior choices to be modified.

EXEC sp_configure ‘present superior choices’, 1

— To replace the presently configured worth for superior choices.

RECONFIGURE

— To allow the characteristic.

EXEC sp_configure ‘xp_cmdshell’, 1

— To replace the presently configured worth for this characteristic.

RECONFIGURE

 

OR we are able to allow this selection from SSMS (SQL Server Administration Studio):

1. From object explorer proper click on on the server
2. Click on “Sides”
3. From Sides drop down listing choose “Floor Space Configuration”
4. Change the worth of “XPCmdShellEnabled” to “True” and OK.

Now we’re going to retailer the database diagram right into a file.

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

DECLARE @sql varchar(8000) 

SET @sql=‘BCP “choose definition from [‘+db_name()+‘].dbo.sysdiagrams the place title = ”DB_DIAGRAM_NAME”” queryout c:TARGET_FOLDERDiagram -c -t, -T -S’+@@SERVERNAME

exec xp_cmdshell @sql

 

Essential Notes: You want to pay attention to some essential issues concerning the code above:

1. The output of the question will create the file in native machine that hosts the SQL Server database
2. The service account for the SQL Server ought to have write permissions to “TARGET_FOLDER”
3. If you wish to save the file in a networked shared folder it is advisable change the goal folder’s path from “c:TARGET_FOLDERDiagram” to “COMPUTER_NAMEC$TARGET_FOLDERDiagram” and once more the service account of the SQL Server ought to have write permissions to the community shared folder. If SQL Server is operating beneath a neighborhood service account you may must outline a proxy account and grant entry rights to it.
4. The output file shall be over written everytime you run the question

After operating the above question a file shall be created within the goal folder containing the database diagram’s content material in binary.

Now that we’ve saved the content material of the diagram in a file format, we have to create the diagram within the different computer systems. Clearly the opposite computer systems’ SQL Server service accounts ought to have learn entry rights to the “TARGET_FOLDER” that saved within the “COMPUTER_NAMEC$” path. By operating the next code you possibly can create the precise copy of the unique diagram within the different machines:

createdesk #tbl (def varbinary(max))

bulkinsert #tbl from ‘C:TARGET_FOLDERDiagram‘

insert into sysdiagrams

              (title,principal_id,model,definition)

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

drop desk #tbl

 

NOTE: In case you are simply testing the entire resolution in a single pc the above code will work completely, however,  if you wish to learn the file from a community shared folder then the “bulk_insert” command ought to level to the community shared folder path like “COMPUTER_NAMEC$TARGET_FOLDERDiagram”. So the second line of the above code needs to be one thing like this:

bulk insert #tbl from ‘COMPUTER_NAMEC$TARGET_FOLDERDiagram‘

All performed!