ER図のリバース生成（SQL Server）について

[カノウ]

「A5：SQL Mk-2」を使用させていただいています。

SQL Server にてER図のリバース生成を行った場合ですが、「エンティティ」タブの論理名の取得方法が変更されていますか？
以前のバージョンとは異なった結果になります。

「エンティティ」タブの論理名は、独自で作成している拡張プロパティ「TableNote」の１行目がセットされます。
※「エンティティ」タブのコメントに、独自で作成している拡張プロパティ「TableNote」の２行目以降がセットされます。

論理名の取得は、SQL Server の標準拡張プロパティ名「MS_Description」が源泉だったと思います。
「属性（列）」の論理名は、標準拡張プロパティ名「MS_Description」が源泉として動作しているので、以前と変わりない結果になります。

以前のバージョンと同様な動作にしたいのですが、どのように対応すればよろしいでしょうか？

※出来ましたら、下記項目のリバース生成時の源泉となる拡張プロパティ名を指定できると助かります。
　・「エンティティ」タブの論理名
　・「エンティティ」タブのコメント
　・「属性（列）」の論理名
　・「属性（列）」のコメント

[松原正和]

カノウさんこんにちは。
　
SQL Server のテーブルコメント・テーブルカラムコメントですが、少なくともここ何年か変更していないはずで、MS_Description から取得しています。
　
テーブルコメントの取得のために次のようなSQLが発行されています。

select s.name as SCHEMA_NAME, t.name as TABLE_NAME , ep.value as COMMENTS from sys.tables t inner join sys.extended_properties ep on ep.major_id = t.object_id inner join sys.schemas s on s.schema_id = t.schema_id where ep.class=1 and ep.name='MS_Description' and ep.minor_id=0 and s.name = 'スキーマ名' union all select s.name as SCHEMA_NAME, t.name as TABLE_NAME , ep.value as COMMENTS from sys.views t inner join sys.extended_properties ep on ep.major_id = t.object_id inner join sys.schemas s on s.schema_id = t.schema_id where ep.class=1 and ep.name='MS_Description' and ep.minor_id=0 and s.name = 'スキーマ名' order by s.name, t.name

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35

select   s.name as SCHEMA_NAME, t.name as TABLE_NAME   , ep.value as COMMENTS from   sys.tables t   inner join sys.extended_properties ep     on ep.major_id = t.object_id   inner join sys.schemas s     on s.schema_id = t.schema_id where   ep.class=1   and ep.name='MS_Description'   and ep.minor_id=0     and s.name = 'スキーマ名' union all select   s.name as SCHEMA_NAME, t.name as TABLE_NAME   , ep.value as COMMENTS from   sys.views t   inner join sys.extended_properties ep     on ep.major_id = t.object_id   inner join sys.schemas s     on s.schema_id = t.schema_id where   ep.class=1   and ep.name='MS_Description'   and ep.minor_id=0     and s.name = 'スキーマ名' order by   s.name, t.name

このSQLで正しく取得できますか？。

[カノウ]

SQL を実行してみました。
「SQL Server 2005 – 9.00.4266.00 (X64)」では、問題なく取得できました。

スミマセン。
今回の事象は、ADO接続の「SQL Server 2000 – 8.00.2039 (Intel X86)」で起きました。
　（前の投稿時に記述するのを失念していました）
上記のSQLは、SQL Server 2000 ではシステムカタログ周りのテーブルが異なるため実行できません。

ADO接続の固有の問題でしょうか。

[松原正和]

カノウさんこんにちは。
　
SQL Server 2000ですか。SQL Server 2000の動作する環境が手元にないので、何ともですが、以下のSQLが実行されます。

select col.name as COLUMN_NAME , value as COLUMN_COMMENT from sysobjects tbl inner join syscolumns col on tbl.id=col.id inner join sysproperties cmt on col.id=cmt.id and colid=smallid where tbl.name='テーブル名' order by colid

1 2 3 4 5 6 7 8 9 10 11 12 13 14

select   col.name as COLUMN_NAME   , value as COLUMN_COMMENT from   sysobjects tbl   inner join syscolumns col     on tbl.id=col.id   inner join sysproperties cmt     on col.id=cmt.id     and colid=smallid where   tbl.name='テーブル名' order by   colid

　
これで、うまく実行できますか？。

• この返信は3年前に松原正和が編集しました。

[カノウ]

返信ありがとうございます。
<br/>
SQL文を見ましたが、コレは「属性 － 論理名」取得についてのSQLではないでしょうか。
※「属性 － 論理名」の取得は正常に動作しています。
※カラムに対して独自定義の拡張プロパティを設定していても、「属性 － 論理名」取得には標準の拡張プロパティ名「MS_Description」が優先されているようです。
<br/>
「エンティティ － 論理名」の取得について、独自定義の拡張プロパティが優先されています。
標準の拡張プロパティ名「MS_Description」から取得されません。
<br/>
お手数をお掛けいたしますが、よろしくお願いいたします。

[松原正和]

カノウさんこんにちは。
　
失礼しました。テーブルの方ですね。

select &#039;dbo&#039; as SCHEMA_NAME, tbl.name as TABLE_NAME , value as COMMENTS from sysobjects tbl inner join sysproperties cmt on tbl.id=cmt.id and cmt.smallid = 0 order by tbl.name;

1 2 3 4 5 6 7 8 9 10 11

select   'dbo' as SCHEMA_NAME, tbl.name as TABLE_NAME   , value as COMMENTS from   sysobjects tbl   inner join sysproperties cmt     on tbl.id=cmt.id     and cmt.smallid = 0 order by   tbl.name;

こちらが、A5:SQL Mk-2でSQL Server 2000 に対してテーブルのコメントを取得するSQLですが、これはもう10年以上手を入れていないSQLです。予想ですが、sysproperties の name 列で絞り込みを行わなければならないのをしておらず、たまたま取得できた順序に依存して動作が変わってしまっているような気がします。
カノウさんの方で、SQLをどう直せばよいかわかりますでしょうか？。
（on句の条件式に、”and cmt.name = ‘MS_Description'” を追加すればよいなど。）

[カノウ]

返信ありがとうございます。

SQLの動作確認を、「SQL Server 2000」上にて実行してみました。
下記のSQL文にて、標準拡張プロパティの値を間違いなく取得できます。
※ WHERE句の条件式、またはON句の条件式への追加で間違いないです。


/* テーブル コメント 取得 */
SELECT
‘dbo’ AS SCHEMA_NAME
, tbl.name AS TABLE_NAME
, cmt.value AS COMMENTS
FROM
sysobjects AS tbl
INNER JOIN sysproperties AS cmt
ON cmt.id = tbl.id
AND cmt.smallid = 0
AND cmt.name LIKE ‘MS_%’
ORDER BY
tbl.name
;

/* カラム コメント 取得 */
SELECT
col.name AS COLUMN_NAME
, cmt.value AS COLUMN_COMMENT
FROM
sysobjects AS tbl
INNER JOIN syscolumns AS col
ON tbl.id = col.id
INNER JOIN sysproperties AS cmt
ON cmt.id = col.id
AND cmt.smallid = col.colid
AND cmt.name LIKE ‘MS_%’
WHERE
tbl.name = ‘テーブル名’
ORDER BY
col.colid
;


※テーブルコメントの取得についても標準拡張プロパティの絞り込みをしています。

よろしくお願いいたします。

[カノウ]

投稿の方法がよく分かっておらず、コードの表示がうまく出来ませんでした。（ムズカシイ）
※ 空行の表示とか、なかなか思い通りにできないデス。

/* テーブル コメント 取得 */ SELECT &#039;dbo&#039; AS SCHEMA_NAME , tbl.name AS TABLE_NAME , cmt.value AS COMMENTS FROM sysobjects AS tbl INNER JOIN sysproperties AS cmt ON cmt.id = tbl.id AND cmt.smallid = 0 AND cmt.name LIKE &#039;MS_%&#039; ORDER BY tbl.name ;

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

/*  テーブル コメント 取得  */ SELECT       'dbo'         AS  SCHEMA_NAME     , tbl.name      AS  TABLE_NAME     , cmt.value     AS  COMMENTS FROM     sysobjects  AS  tbl     INNER JOIN  sysproperties   AS  cmt         ON  cmt.id = tbl.id         AND cmt.smallid = 0         AND cmt.name LIKE 'MS_%' ORDER BY     tbl.name ;

/* カラム コメント 取得 */ SELECT col.name AS COLUMN_NAME , cmt.value AS COLUMN_COMMENT FROM sysobjects AS tbl INNER JOIN syscolumns AS col ON tbl.id = col.id INNER JOIN sysproperties AS cmt ON cmt.id = col.id AND cmt.smallid = col.colid AND cmt.name LIKE &#039;MS_%&#039; WHERE tbl.name = &#039;mg1p&#039; ORDER BY col.colid ;

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

/*  カラム コメント 取得    */ SELECT       col.name      AS  COLUMN_NAME     , cmt.value     AS  COLUMN_COMMENT FROM     sysobjects  AS  tbl     INNER JOIN  syscolumns  AS  col         ON  tbl.id = col.id     INNER JOIN  sysproperties   AS  cmt         ON  cmt.id = col.id         AND cmt.smallid = col.colid         AND cmt.name LIKE 'MS_%' WHERE     tbl.name = 'mg1p' ORDER BY     col.colid ;

[松原正和]

カノウさんこんにちは。

回答ありがとうございます。= による完全一致ではなく、LIKEによる前方一致なのは理由があるのでしょうか？。

[カノウ]

松原さん、こんばんわ。

「LIKEによる前方一致なのは理由があるのでしょうか？」ですが、絶対的な理由はありません。

「= による完全一致」で問題ないですが、下記の様な余計な事まで考えた結果です。

• 「MS_Description」以外のＭＳ標準拡張プロパティ名が発生した。
• 標準拡張プロパティ名「MS_Description」が微妙に変更された。

スミマセン。

[松原正和]

カノウさんこんにちは。
　
今回のSQLはSQL Server 2000 のみで実行されるSQLなので、今後変更されることはないはずと考えられることから、”and cmt.name = ‘MS_Description’” で対処することにします。
　
Version 2.17.0 beta 21 で対処しましたのでご確認ください。

[投稿者]

投稿