SQL Server - 使用聚集索引时如何存储数据页

Question

EngineeringSQL

Asked: 2012-08-23 07:54:35 +0800 CST2012-08-23 07:54:35 +0800 CST 2012-08-23 07:54:35 +0800 CST

如何在 SQL Server (2005/2008) 中合并两个 XML 变量？

772

转发：我已经阅读了这个 StackOverflow 线程，它没有解决我需要的深度保留。

我想使用层次结构创建一个包含单个数据库的数据定义对象的 XML 文档：

<CATALOG>
  <SCHEMA>
    <TABLE>
      <TableExtendedProperty/>
      <TableExtendedProperty/>
      <COLUMN>
        <ColumnExtendedProperty/>
        <ColumnExtendedProperty/>
      </COLUMN>
      <COLUMN>
        <ColumnExtendedProperty/>
        <ColumnExtendedProperty/>
      </COLUMN>
      ...etc...
    </TABLE>
    ...etc...
  </SCHEMA>
  ...etc...
</CATALOG>

我构建了两个查询。

使用子 TableExtendedProperty 元素创建到 TABLE 级别的层次结构
使用子 ColumnExtendedProperty 元素创建到 COLUMN 级别的层次结构

下面提供了每个查询的（缩写）XML 结果。

我需要 (a) 将这两个 XML 变量合并到一个 XML 变量中，或者 (b) 构造一个不同的查询来产生所需的结果。

你能推荐什么？

源代码

Query creating hierarchy to the TABLE level with child TableExtendedProperty elements

SELECT DISTINCT
_Catalog.TABLE_CATALOG
,_Schema.TABLE_SCHEMA
,_Table.TABLE_NAME
,_TableExtProp.name as TableExtPropName
,_TableExtProp.value as TableExtPropValue
from information_schema.tables _Schema
INNER JOIN information_schema.columns _Catalog ON _Catalog.TABLE_CATALOG=_Schema.TABLE_CATALOG
INNER JOIN information_schema.tables _Table ON _Table.TABLE_NAME=_Schema.TABLE_NAME 
LEFT OUTER join sys.extended_properties _TableExtProp on _TableExtProp.major_id=OBJECT_ID(_Schema.TABLE_SCHEMA + '.' + _Table.TABLE_NAME) and _TableExtProp.minor_id=0
FOR XML AUTO

RESULT
<_Catalog TABLE_CATALOG="A">
    <_Schema TABLE_SCHEMA="B">
        <_Table TABLE_NAME="C">
            <_TableExtProp TableExtPropName="TP1" TableExtPropValue="Lorem" />
            <_TableExtProp TableExtPropName="TP2" TableExtPropValue="Ipsum" />
        </_Table>
        <_Table TABLE_NAME="D">
            <_TableExtProp TableExtPropName="TP1" TableExtPropValue="Borem" />
        </_Table>
    </_Schema>
    <_Schema TABLE_SCHEMA="E">
        <_Table TABLE_NAME="F">
        </_Table>
        <_Table TABLE_NAME="G">
            <_TableExtProp TableExtPropName="TP1" TableExtPropValue="Corem" />
        </_Table>
    </_Schema>
</_Catalog>


Query creating hierarchy to the COLUMN level with child ColumnExtendedProperty elements

SELECT DISTINCT
_Catalog.TABLE_CATALOG
,_Schema.TABLE_SCHEMA
,_Table.TABLE_NAME
,_Col.COLUMN_NAME
,_ColumnExtProp.name as ColumnExtPropName
,_ColumnExtProp.value as ColumnExtPropValue
from information_schema.tables _Schema
INNER JOIN information_schema.columns _Catalog ON _Catalog.TABLE_CATALOG=_Schema.TABLE_CATALOG
INNER JOIN information_schema.tables _Table ON _Table.TABLE_NAME=_Schema.TABLE_NAME 
INNER JOIN information_schema.columns _Col  ON _col.Table_name=_Table.Table_Name /* need a schema comparison also */
LEFT OUTER join sys.extended_properties _ColumnExtProp on _ColumnExtProp.major_id=OBJECT_ID(_Schema.TABLE_SCHEMA + '.' + _Table.TABLE_NAME) and _ColumnExtProp.minor_id=_Col.ORDINAL_POSITION
FOR XML AUTO

RESULT
  <_Catalog TABLE_CATALOG="A">
    <_Schema TABLE_SCHEMA="B">
      <_Table TABLE_NAME="C">
        <_Col COLUMN_NAME="AXA">
          <_ColumnExtProp ColumnExtPropName="CP1" ColumnExtPropValue="WWW" />
          <_ColumnExtProp ColumnExtPropName="CP2" ColumnExtPropValue="WWW" />
        </_Col>
        <_Col COLUMN_NAME="FRDCQ">
          <_ColumnExtProp ColumnExtPropName="CP1" ColumnExtPropValue="EQW" />
        </_Col>
      </_Table>
      <_Table TABLE_NAME="D">
        <_Col COLUMN_NAME="PolicyTypeCode">
          <_ColumnExtProp ColumnExtPropName="CP3" ColumnExtPropValue="SAS" />
        </_Col>
      </_Table>
    </_Schema>
    <_Schema TABLE_SCHEMA="E">
      <_Table TABLE_NAME="F">
        <_Col COLUMN_NAME="BAXA">
        </_Col>
        <_Col COLUMN_NAME="BAFA">
          <_ColumnExtProp ColumnExtPropName="CP1" ColumnExtPropValue="WEW" />
        </_Col>
      </_Table>
      <_Table TABLE_NAME="G">
        <_Col COLUMN_NAME="BAFA">
          <_ColumnExtProp ColumnExtPropName="CP1" ColumnExtPropValue="WTTEW" />
        </_Col>
      </_Table>
    </_Schema>
  </_Catalog>


DESIRED RESULT
  <_Catalog TABLE_CATALOG="A">
    <_Schema TABLE_SCHEMA="B">
      <_Table TABLE_NAME="C">
        <_TableExtProp TableExtPropName="TP1" TableExtPropValue="Lorem" />
        <_TableExtProp TableExtPropName="TP2" TableExtPropValue="Ipsum" />
        <_Col COLUMN_NAME="AXA">
          <_ColumnExtProp ColumnExtPropName="CP1" ColumnExtPropValue="WWW" />
          <_ColumnExtProp ColumnExtPropName="CP2" ColumnExtPropValue="WWW" />
        </_Col>
        <_Col COLUMN_NAME="FRDCQ">
          <_ColumnExtProp ColumnExtPropName="CP1" ColumnExtPropValue="EQW" />
        </_Col>
      </_Table>
      <_Table TABLE_NAME="D">
        <_TableExtProp TableExtPropName="TP1" TableExtPropValue="Borem" />
        <_Col COLUMN_NAME="PolicyTypeCode">
          <_ColumnExtProp ColumnExtPropName="CP3" ColumnExtPropValue="SAS" />
        </_Col>
      </_Table>
    </_Schema>
    <_Schema TABLE_SCHEMA="E">
      <_Table TABLE_NAME="F">
        <_Col COLUMN_NAME="BAXA">
        </_Col>
        <_Col COLUMN_NAME="BAFA">
          <_ColumnExtProp ColumnExtPropName="CP1" ColumnExtPropValue="WEW" />
        </_Col>
      </_Table>
      <_Table TABLE_NAME="G">
        <_TableExtProp TableExtPropName="TP1" TableExtPropValue="Corem" />
        <_Col COLUMN_NAME="BAFA">
          <_ColumnExtProp ColumnExtPropName="CP1" ColumnExtPropValue="WTTEW" />
        </_Col>
      </_Table>
    </_Schema>
  </_Catalog>

1 个回答

Voted

Mikael Eriksson · Answer 1 · 2012-08-23T12:26:27+08:00

您可以使用for xml path而不是for xml raw. 在字段列表中的相关子查询中构建您的层次结构。您还可以order by在子查询中使用按字母顺序排列的表和按顺序排列的列ORDINAL_POSITION。

我试图翻译你所拥有的并想出了这个。

select T1.TABLE_CATALOG as "@TABLE_CATALOG",
       (
       select T2.TABLE_SCHEMA as "@TABLE_SCHEMA",
              (
              select T3.TABLE_NAME as "@TABLE_NAME",
                     (
                     select P.name as "@TableExtPropName",
                            P.value as "@TableExtPropValue"
                     from sys.extended_properties as P
                     where P.major_id = object_id(T2.TABLE_SCHEMA+'.'+T3.TABLE_NAME) and
                           P.minor_id = 0
                     for xml path('_TableExtProp'), type
                     ),
                     (
                     select C.COLUMN_NAME as "@COLUMN_NAME",
                            (
                            select P.name as "@ColumnExtPropName",
                                   P.value as "@ColumnExtPropValue"
                            from sys.extended_properties as P
                            where P.major_id = object_id(T2.TABLE_SCHEMA+'.'+ T3.TABLE_NAME) and 
                                  P.minor_id = C.ORDINAL_POSITION
                            for xml path('_ColumnExtProp'), type
                            )
                     from INFORMATION_SCHEMA.COLUMNS as C
                     where C.TABLE_NAME = T3.TABLE_NAME and
                           C.TABLE_SCHEMA = T2.TABLE_SCHEMA
                     order by C.ORDINAL_POSITION
                     for xml path('_Col'), type
                     )
              from INFORMATION_SCHEMA.TABLES as T3
              where T3.TABLE_SCHEMA = T2.TABLE_SCHEMA and
                    T3.TABLE_CATALOG = T1.TABLE_CATALOG
              order by T3.TABLE_NAME
              for xml path('_Table'), type
              )
       from INFORMATION_SCHEMA.TABLES as T2
       where T1.TABLE_CATALOG = T2.TABLE_CATALOG
       group by T2.TABLE_SCHEMA
       order by T2.TABLE_SCHEMA
       for xml path('_Schema'), type 
       )
from INFORMATION_SCHEMA.TABLES as T1
group by T1.TABLE_CATALOG
for xml path('_Catalog')

有了这个表在数据库中DBName。

CREATE TABLE [dbo].[TableName](
    [ID] [int] NOT NULL,
    [Name] [nchar](10) NULL
) ON [PRIMARY]

GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Column description' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'TableName', @level2type=N'COLUMN',@level2name=N'ID'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Table description' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'TableName'
GO

输出是。

<_Catalog TABLE_CATALOG="DBName">
  <_Schema TABLE_SCHEMA="dbo">
    <_Table TABLE_NAME="TableName">
      <_TableExtProp TableExtPropName="MS_Description" TableExtPropValue="Table description" />
      <_Col COLUMN_NAME="ID">
        <_ColumnExtProp ColumnExtPropName="MS_Description" ColumnExtPropValue="Column description" />
      </_Col>
      <_Col COLUMN_NAME="Name" />
    </_Table>
  </_Schema>
</_Catalog>

如何在 SQL Server (2005/2008) 中合并两个 XML 变量？

如何查看 Oracle 中的数据库列表？

mysql innodb_buffer_pool_size 应该有多大？

列出指定表的所有列

从 .frm 和 .ibd 文件恢复表？

如何在不修改我自己的 tnsnames.ora 的情况下使用 sqlplus 连接到位于另一台主机上的 Oracle 数据库

你如何mysqldump特定的表？

如何选择每组的第一行？

使用 psql 列出数据库权限

如何从 PostgreSQL 中的选择查询中将值插入表中？

如何使用 psql 列出所有数据库和表？

如何在 SQL Server (2005/2008) 中合并两个 XML 变量？

1 个回答

相关问题