如何从 SQL Server 2008 中的现有数据库创建 XML 架构 [英] how to create XML schema from an existing database in SQL Server 2008

问题描述

有没有办法从 SQL Server 2008、SQL Server Management Studio 中的现有数据库创建 XML 架构?

Is there a way to create an XML schema from an existing database in SQL Server 2008, SQL Server Management Studio?

我有一个大约有 50 个表的数据库.我希望创建一个漂亮"的图表来显示这些表之间的关系.使用另一个名为 SQL Designer (https://code.google.com/p/wwwsqldesigner/) 的应用程序会给我漂亮"的图片，但我不知道如何创建所需的 XML 模式.

I have a DB with ~50 tables. I'm looking to create a "nice" diagram showing the relationship between those tables. Using another application called SQL Designer (https://code.google.com/p/wwwsqldesigner/) will give me the "nice" looking picture, but I don't know how to create the XML schema required.

我在论坛(和 MS)上进行了搜索，但没有完全找到我的答案.我可以找到创建数据库的工具，但我正在反其道而行之……我需要一张漂亮的图片，以图表形式显示数据库.我想如果我可以将我的数据库结构转换为 XML，那么 SQL Designer 将为我完成剩下的工作.

I did a search across the forum (and MS) and couldn't quite find my answer. I could find tools which created a database, but I'm looking at the reverse... I need a pretty picture which shows the database in diagrammatic form. I thought if I could get my db structure into XML then SQL Designer will do the rest for me.

感谢您的帮助.

尼克

推荐答案

如果您只需要表的 xml 架构，请使用以下命令查询它们:

If you only need the xml schema of tables query them with this:

select top 0 * FROM daTable FOR XML AUTO,XMLSCHEMA

如果您需要表名和列来创建数据库的表示以及表的连接方式，您可以使用以下内容:

If you need the table names and columns in order to create a representation of your database and how tables are connected you can use something like this:

SELECT
s.name as '@Schema'
,t.name as '@Name'
,t.object_id as '@Id'
,(
    SELECT c.name as '@Name'
    ,c.column_id as '@Id'
    ,IIF(ic.object_id IS NOT NULL,1,0) as '@IsPrimaryKey'
    ,fkc.referenced_object_id as '@ColumnReferencesTableId'
    ,fkc.referenced_column_id as '@ColumnReferencesTableColumnId'
    FROM sys.columns as c
    LEFT OUTER JOIN sys.index_columns as ic
        ON c.object_id = ic.object_id
        AND c.column_id = ic.column_id
        AND ic.index_id = 1
    LEFT OUTER JOIN sys.foreign_key_columns as fkc
        ON c.object_id = fkc.parent_object_id
        AND c.column_id = fkc.parent_column_id
    WHERE c.object_id = t.object_id
    FOR XML PATH ('Column'),TYPE
)
FROM sys.schemas as s
INNER JOIN sys.tables as t
    ON s.schema_id = t.schema_id
FOR XML PATH('Table'),ROOT('Tables')

让您的应用程序使用 ColumnReferencesTableId 和 ColumnReferencesTableColumnId 来获取表关系.如果您更喜欢写出它们的名称，您还可以进一步连接到被引用的列和表，但我认为它们的 ID 就足够了.

Let your application use the ColumnReferencesTableId and ColumnReferencesTableColumnId to get table relations. You could also further join back to columns and tables which are referenced if you prefer writing their names out but I thought their Ids would suffice.

这篇关于如何从 SQL Server 2008 中的现有数据库创建 XML 架构的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！