In the previous article we discussed about how migrating a database diagram into another database. In this article I’ll quickly show you how to install database diagram support. As mentioned in the” How to copy or migrate database diagrams into another database” article, we need to install database diagram support to be able to see the migrated database diagrams. We also explained a very easy way to install database diagram support from SSMS in the “How to store a SQL Server database diagram into a file and share it with others?” article. Now, assume that we want to migrate the database diagrams into several SQL Server instances. It seems that it might be better if we can implement the whole process programmatically. This was exactly my question when I wanted to deploy several database diagrams from a database hosted in development environment into a copy of that database hosted by test or UAT (User Acceptance Test) environments. For instance, just assume that there are a bunch of database diagrams created by developers in development environment. So, you’ll have an exact copy of the database structure in test environment. Your testers need to use the database diagrams created by the developers. So far, so good. This part of the challenge is covered in the previous articles. However, we still need to install database diagram support manually and this is what we don’t like! The solution is really easy. Run the following code and you are done! You can also add the following code to the execute SQL task from your SSIS package if you decided to implement the solution in an SSIS package (take a look at “Migrating database diagram by creating a simple SSIS package” No. 8).
We discussed in one of the previous articles called “How to store a SQL Server database diagram into a file and share it with others?” we can store database diagrams in files and share the files with others. In this article I’m describing very fast and easy ways to make a copy of existing database diagrams into another database. The possible scenarios are:
1. We want to create a copy of database diagrams into another database in the same SQL Server instance
2. We want to make a copy of database diagrams in another instance of SQL server
In both cases we need to have write access permission on the destination database.
Migrating database diagrams between two databases in the same instance
We just need to run the following T-SQL script:
IF OBJECT_ID(N’dbo.sysdiagrams’) IS NULL
CREATE TABLE dbo.sysdiagrams
name sysname NOT NULL,
principal_id int NOT NULL,
diagram_id int PRIMARY KEY IDENTITY,
CONSTRAINT UK_principal_name UNIQUE
EXEC SYS.SP_MS_MARKSYSTEMOBJECT ‘sysdiagrams’ —Creating a system object
Insert into sysdiagrams (name,principal_id,version,definition)
select name,principal_id,version,definition from SOURCEDB.dbo.sysdiagrams
The above solution works even if you did not install diagram support and you’ll have the copy of diagrams in place immediately after installing diagram support. To install database diagram support:
1. Expand the destination database
2. Right click on “Database diagrams”
3. Click “Install Diagram Support”
It is really easy. You just need to add the object name as a parameter to “SYS.SP_MS_MARKSYSTEMOBJECT”. In the following code we are creating a system table named“TestSysTable”:
IF OBJECT_ID(N’TestSysTable’) IS NULL
CREATE TABLE dbo.TestSysTable
C1 int NOT NULL,
C2 int NOT NULL,
C3 int PRIMARY KEY IDENTITY
EXEC SYS.SP_MS_MARKSYSTEMOBJECT ‘TestSysTable’
If you are working in a company that your managers are getting database documentation seriously, thumbs up! One of the ways to write useful documentation that is really effective is using SQL Server extended properties.
We can add extended properties to almost all SQL Server objects by right clicking on the object-> select properties-> select “Extended Properties” and add new properties to the object.
So, after adding the new properties we might need to query those properties in the future.
Executing the following T-SQL script retrieves what we need:
select O.name ObjectName, e.name PropertyName, value
from sys.extended_properties e inner join sys.objects o on e.major_id=o.object_id