如何将SQL Server 2008数据库图导出到另一个数据库？ [英] How to export a SQL Server 2008 Database Diagram to another DB?

问题描述

我使用SQL Server 2008中方便的数据库图表工具来创建和管理关系。我已经将sourceDB导出到destinationDB，但该图没有出现。

I use the handy Database Diagramming tool in SQL Server 2008 for creating and managing relationships. I have exported the sourceDB to the destinationDB but the diagram doesn't come across.

我正在四处寻找试图导出仅包含其中一个图的方法。数据库到另一个数据库...此在线KB文章失败，因为从dtproperties中选择* 不再存在。

I am looking around trying to figure out how to export just the diagram I have in one database to another... This online KB article fails since select * from dtproperties doesn't exist anymore.

推荐答案

@Ash我遇到了同样的问题。这是我们为解决该问题所做的工作...

@Ash I was having the same problem. Here's what we did to get around it...

似乎系统图存储在 sysdiagrams表中。因此，您需要做的第一件事是确定要复制的逻辑示意图的diagram_id。运行以下查询以将其全部列出。 **请注意，您需要用数据库名称替换 SourceDB。

It seems that System Diagrams are stored within the "sysdiagrams" table. So the first thing you need to do is determine the diagram_id of the Diagram you wish to copy. Run the following query to list them all. ** Note you need to replace "SourceDB" with the name of your database.

-- List all database diagrams
SELECT * FROM [SourceDB].[dbo].sysdiagrams

然后您可以使用INSERT复制从一个数据库到另一个数据库的关系图如下。 **请再次注意，将 SourceDB替换为包含现有图表的数据库的名称，将 DestinationDB替换为要复制到的数据库的名称。还应将@SourceDiagramId设置为上面获取的ID。

Then you can use INSERT to duplicate the diagram from one database to another as follows. ** Note again replace "SourceDB" with the name of the Database containing the existing diagram and "DestinationDB" with the name of the Database you wish to copy to. Also @SourceDiagramId should be set to the id retrieved above.

-- Insert a particular database diagram
DECLARE @SourceDiagramId int = 1

INSERT INTO [DestinationDB].[dbo].sysdiagrams
SELECT [name],diagram_id , version,definition from [SourceDB].[dbo].sysdiagrams
WHERE diagram_id = @SourceDiagramId

然后，您需要将 principal_id手动设置为1。

Then you need to set the "principal_id" to 1 manually.

-- Update the principal id (no idea why, but it set the owner as some asp_net user
UPDATE [DestinationDB].[dbo].sysdiagrams
SET principal_id = 1

这对我们来说似乎很hacky尤其是因为该图完全存储在单个二进制字段定义中。

This worked for us it seems pretty hacky especially since the Diagram is stored entirely in a single binary field "definition".

答案来自：
http://www.dotnetspider.com/resources/21180-Copy-or -移动-database-digram-from-for.aspx

这篇关于如何将SQL Server 2008数据库图导出到另一个数据库？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！