将数据库模式导出为XML文件

Question

我希望能够将整个数据库模式导出到XML文件中，需要的信息如下：

1. 表 - 列、数据类型、主键、外键
2. 视图 - 返回列
3. 函数和存储过程 - 参数、返回列和数据类型

我在Google等搜索引擎中找不到任何相关信息...是否有人曾经遇到过类似的问题？

- Sebastian Xawery Wiśniowiecki

---

你可以将所有这些事情编写成脚本，然后转换为XML吗？你到目前为止尝试了什么？ - NickyvV

我在 SQL 方面不是很擅长。到目前为止，我已经知道如何使用表格来完成它，但我还需要视图、函数和存储过程。 - Sebastian Xawery Wiśniowiecki

Answer 1

通过此查询，您可以获取表格和视图：

SELECT TABLE_NAME AS '@Name', CASE WHEN TABLE_TYPE = 'BASE TABLE' THEN 'Table' ELSE 'View' END AS '@Type',
(
    SELECT Column_Name as '@Name',
            DATA_TYPE as '@DataType',
            case data_type 
                when 'nvarchar' 
                then CHARACTER_MAXIMUM_LENGTH 
                when 'varchar'  
                then CHARACTER_MAXIMUM_LENGTH
                else null 
            end  as '@Length',
            IS_NULLABLE AS '@IsNullable',
            COLUMNPROPERTY(OBJECT_ID(TABLE_NAME), COLUMN_NAME, 'IsIdentity') AS '@IsIdentity',

            (SELECT tc.CONSTRAINT_TYPE FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE cu ON tc.CONSTRAINT_NAME = cu.CONSTRAINT_NAME
WHERE tc.TABLE_NAME = INFORMATION_SCHEMA.COLUMNS.TABLE_NAME AND cu.COLUMN_NAME = INFORMATION_SCHEMA.COLUMNS.Column_Name) AS '@Constraint'

    FROM INFORMATION_SCHEMA.COLUMNS 
    where INFORMATION_SCHEMA.COLUMNS.TABLE_NAME = 
        INFORMATION_SCHEMA.TABLES.TABLE_NAME
    order by INFORMATION_SCHEMA.COLUMNS.ORDINAL_POSITION
    For XML PATH ('Column'), type
)

FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA='dbo'
ORDER BY TABLE_NAME ASC  
For XML PATH ('Table'),Root('Tables')

还有这个是针对SPS和FNS的。

 SELECT
      SPECIFIC_SCHEMA     AS '@ObjectSchema'
      ,ROUTINE_NAME       AS '@ObjectName'
      ,ROUTINE_TYPE       AS '@ObjectType'
      ,ROUTINE_DEFINITION AS '@TEXT'
FROM  INFORMATION_SCHEMA.ROUTINES 
WHERE   (ROUTINE_TYPE = 'function' OR ROUTINE_TYPE = 'procedure')
For XML PATH ('Object') , TYPE, ROOT('Objects') 

希望能对你有所帮助。

- MSL

---

对于第一个查询，我遇到了以下错误：Msg 512，级别16，状态1，行1。子查询返回的值超过1个。当子查询跟随=，！=，<，<=，>，>=时，或者当子查询用作表达式时，不允许这种情况发生。如何解决？ - Sathish

Answer 2

你可以在Visual Studio中使用实体数据建模。

用XML文本编辑器打开创建的.edmx文件，或者你需要的任何其他编辑器。

好处是你可以从.edxm文件（在Visual Studio中）生成数据库模型。

- nadavesela

Answer 3

这是@ MSL答案的略微修改版本，可以修复错误：子查询返回多个值。当子查询跟随=，！=，<，<=，>，>=或将子查询用作表达式时，不允许出现此情况。 当表中的列具有多个约束条件时会引起此错误，并且通过将“约束”属性更改为XML文件中“列”的子节点来解决。

SELECT TABLE_NAME AS '@Name', CASE WHEN TABLE_TYPE = 'BASE TABLE' THEN 'Table' ELSE 'View' END AS '@Type',
(
    SELECT Column_Name as '@Name',
        DATA_TYPE as '@DataType',
        case data_type
            when 'nvarchar'
            then CHARACTER_MAXIMUM_LENGTH
            when 'varchar'
            then CHARACTER_MAXIMUM_LENGTH
            else null
        end  as '@Length',
        IS_NULLABLE AS '@IsNullable',
        COLUMNPROPERTY(OBJECT_ID(TABLE_NAME), COLUMN_NAME, 'IsIdentity') AS '@IsIdentity',
        (SELECT tc.CONSTRAINT_TYPE AS '@ConstraintName' FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
            INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE cu ON tc.CONSTRAINT_NAME = cu.CONSTRAINT_NAME
            WHERE tc.TABLE_NAME = INFORMATION_SCHEMA.COLUMNS.TABLE_NAME AND cu.COLUMN_NAME = INFORMATION_SCHEMA.COLUMNS.Column_Name
            For XML PATH ('Constraint'), type )
    FROM INFORMATION_SCHEMA.COLUMNS
    where INFORMATION_SCHEMA.COLUMNS.TABLE_NAME =
        INFORMATION_SCHEMA.TABLES.TABLE_NAME
    order by INFORMATION_SCHEMA.COLUMNS.ORDINAL_POSITION
    For XML PATH ('Column'), type
)

FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA='dbo'
ORDER BY TABLE_NAME ASC
For XML PATH ('Table'),Root('Tables') 

- Erik McKelvey