Tips on how to retailer a SQL Server database diagram right into a file and share it with others?


Storing the database diagrams’ information 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)

image

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 staff and as quickly as they run the question they are going to have precisely the identical database diagram of their database. Word that they need to set up diagram help first.

All executed.

  • Storing the database diagram’s information 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 information 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 choice that allows system admins to manage the flexibility of executing xp_cmdshell on a system. So, for safety causes this feature 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 part ‘xp_cmdshell’ as a result of this part 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 choice 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 will allow this feature 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 record choose “Floor Space Configuration”
  4. Change the worth of “XPCmdShellEnabled” to “True” and OK.

image

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

 

Vital Notes: You want to pay attention to some essential issues in regards to 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 that you must 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 working beneath an area service account you would possibly must outline a proxy account and grant entry rights to it.
  4. The output file will probably be over written everytime you run the question

After working the above question a file will probably 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 working the next code you may 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: If you’re simply testing the entire resolution in a single laptop 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 executed!



Supply hyperlink

Leave a Reply

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