How to copy or migrate database diagrams into another database

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:

use DESTINATIONDB

IF OBJECT_ID(N’dbo.sysdiagrams’) IS  NULL

begin

              CREATE TABLE dbo.sysdiagrams

              (

                     name sysname NOT NULL,

                     principal_id int NOT NULL,

                     diagram_id int PRIMARY KEY IDENTITY,

                     version int,

      

                     definition varbinary(max)

                     CONSTRAINT UK_principal_name UNIQUE

                     (

                           principal_id,

                           name

                     )

              )

EXEC SYS.SP_MS_MARKSYSTEMOBJECT ‘sysdiagrams’ Creating a system object

End

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”

Continue reading “How to copy or migrate database diagrams into another database”

How to create a system object (Table, Stored Procedure, View…)

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

begin

              CREATE TABLE dbo.TestSysTable

              (

                     C1 int NOT NULL,

                     C2 int NOT NULL,

                     C3 int PRIMARY KEY IDENTITY

                    

              )

EXEC SYS.SP_MS_MARKSYSTEMOBJECT ‘TestSysTable’

End

 

All done!

Enjoy!

How to query extended properties

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.

clip_image002

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

 

clip_image003

All done!

How to create schema dynamically using dynamic SQL

In this short post I’ll show you how to create database schema using dynamic SQL.

It’s easy, just take a look at the following code:

declare @SchemaName varchar(max)

set @SchemaName = ‘YOUR_SCHEMA_NAME’

if not exists (select 0 from sys.schemas where name=@SchemaName)

exec(‘create schema [‘+@SchemaName+‘]’)

go

 

Enjoy!