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

このトピックには10件の返信、1人の参加者があり、最後に松原正和により3年前に更新されました。

11件の投稿を表示中 - 1 - 11件目 (全11件中)

投稿者

投稿

2021/10/16 12:07 #9785 返信

カノウ

ゲスト

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

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

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

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

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

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

2021/10/17 16:12 #9792 返信

松原正和

キーマスター

カノウさんこんにちは。
　
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=&#039;MS_Description&#039;
  and ep.minor_id=0

  and s.name = &#039;スキーマ名&#039;
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=&#039;MS_Description&#039;
  and ep.minor_id=0

  and s.name = &#039;スキーマ名&#039;
order by
  s.name, t.name

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で正しく取得できますか？。

2021/10/19 07:19 #9796 返信

カノウ

ゲスト

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

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

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

2021/10/24 14:58 #9814 返信

松原正和

キーマスター

カノウさんこんにちは。
　
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=&#039;テーブル名&#039;
order by
  colid

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年、 1ヶ月前に松原正和が編集しました。

2021/10/24 23:55 #9823 返信

カノウ

ゲスト

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

2021/10/30 23:09 #9845 返信

松原正和

キーマスター

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

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;

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'” を追加すればよいなど。）

2021/11/02 21:07 #9863 返信

カノウ

ゲスト

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

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 ;

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

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

2021/11/02 21:11 #9864 返信

カノウ

ゲスト

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


/*  テーブル コメント 取得  */
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
;

/* テーブルコメント取得 */

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
;

/* カラムコメント取得 */

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

;

2021/11/03 17:43 #9867 返信

松原正和

キーマスター

カノウさんこんにちは。

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

2021/11/03 23:39 #9870 返信

カノウ

ゲスト

松原さん、こんばんわ。

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

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

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

スミマセン。

2021/11/07 13:23 #9876 返信

松原正和

キーマスター

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

投稿者

投稿

11件の投稿を表示中 - 1 - 11件目 (全11件中)