初めに

こちらのSQLはテーブル調査等に使用したときのSQLです
・A5-Mk2で使いやすい感じで書いています

テーブル一覧検索SQL

テーブル一覧取得

指定したスキーマ等のテーブル一覧を出力します。

設定

スキーマ名の指定はなしでもできますが、する場合は
「SchemaName 」で行います

--スキーマ名 ※全ての時は「''」
--*SetParameter SchemaName 'スキーマ名' S

同じくテーブル名称で検索したい場合は
「LikeTblName 」で行います

--検索テーブル名（英語or日本語）
-- 一覧で表示する場合は「'%%'」で検索
--*SetParameter LikeTblName '%テーブル名%' S

SQL文

テーブル一覧取得.sql


-- ==========================================
-- 機能：テーブル名一覧取得
--
--*DataTitle テーブル名
-- 
-- ==========================================

--スキーマ名 ※全ての時は「''」
--*SetParameter SchemaName '' S

--検索テーブル名（英語or日本語）
-- 一覧で表示する場合は「'%%'」で検索
--*SetParameter LikeTblName '%%' S

select
     trim(A.schemaname)   as  スキーマ     --schema
    ,trim(B.description)  as  論理名       --TABLE_COMMENT
    ,trim(A.relname)      as  物理名       --TABLE_NAME
    ,UPPER(A.relname)     as  物理名大文字 --U_TABLE_NAME
from
	 pg_stat_user_tables A
	,pg_description B
where
	A.relname in (
		select 
			relname as TABLE_NAME
		from 
			pg_stat_user_tables
	)
   --スキーマ名
   and case 
          WHEN @SchemaName = '' THEN 1=1
          ELSE A.schemaname = current_schema() 
       END
	and A.relid=B.objoid
	and B.objsubid=0
    and ( UPPER(A.relname) like UPPER(@LikeTblName) --物理名(日本語) 
         OR B.description like @LikeTblName         --論理名(英語)
         ) 
order by A.relname
;

テーブル定義取得

テーブル定義取得(その1)

設定

こちらは「【テーブル名】」の部分にテーブル名称を入れると取得できます

--【テーブル名】
--*SetParameter tblName 'テーブル名（英語）' S

SQL文

テーブル定義取得.sql

-- ==================================================
--*DataTitle カラム名取得(定義書風)
-- ※テーブルのカラム一覧を取得する
-- ==================================================

--【テーブル名】
--*SetParameter tblName 'TBL_XXXXX' S

select
--※テーブルのカラム一覧を取得のとき
    COLUMNS.ordinal_position                                      as 連番
   ,trim(COLUMNS.table_schema)                                    as スキーマ
   ,substr(DESCRI.description,1,strpos(DESCRI.description,':')-1) as 物理名
   ,COLUMNS.column_name                                           as 論理名
   ,COLUMNS.data_type                                             as タイプ
   ,COLUMNS.udt_name                                              as 型
   ,COLUMNS.is_nullable                                           as "NotNull"
   ,substr(DESCRI.description,strpos(DESCRI.description,':')+1)   as コメント

from
  pg_stat_user_tables TABLES
 ,information_schema.columns COLUMNS
 ,pg_description DESCRI
where 1=1
  and TABLES.relname=COLUMNS.table_name
  and TABLES.relid  = DESCRI.objoid 
  and COLUMNS.ordinal_position=DESCRI.objsubid
  and TABLES.schemaname = current_schema()
  and COLUMNS.table_schema = current_schema()
  and TABLES.relname = LOWER(@tblName)
order by COLUMNS.ordinal_position
;

テーブル定義取得(その２)

設定１

同じようにテーブル名を設定します。

--【テーブル名】
--*SetParameter tblName 'テーブル名（英語）' S

設定２

今回別なパターンでコメント部分の記述が違っていて「：」の区切りでは
上手に取得できなかったので「項目区切り文字」「コメント区切り文字」を追加しました。

--テーブル項目区切り文字
--*SetParameter tblNameDelimiter '論理名称：' S
--コメント項目区切り文字
--*SetParameter commentDelimiter 'コメント：' S

SQL文

テーブル定義取得その2.sql


-- ==================================================
--*DataTitle TBL定義書風
-- ※テーブルのカラム一覧を取得する
--
--【出力イメージ】
--  定義書風の感じ
-- ==================================================

-- ■■■■■■■■■■■■■■■■■■■■■■■■■■
--【パラメータ：テーブル名】
--*SetParameter tblName 'TBL_XXXXX' S
--
-- ■■■■■■■■■■■■■■■■■■■■■■■■■■
--テーブル項目区切り文字
--*SetParameter tblNameDelimiter '論理名称：' S
--コメント項目区切り文字
--*SetParameter commentDelimiter 'コメント：' S


select
    0                                               as "No."
   ,'※テーブル情報'                                as 物理名
   ,'【スキーマ：'|| trim(A.schemaname)     || '】' as 論理名
   ,'【物理名：'  || trim(B.description)    || '】' as タイプ
   ,'【論理名：'  || trim(upper(A.relname)) || '】' as 型
   ,null                                            as 文字数   
   ,null                                            as 整数
   ,null                                            as 少数
   ,''                                              as "NotNull"
   ,''                                              as コメント    
from
	 pg_stat_user_tables A
	,pg_description B
where
	A.relname in (
		select 
			relname as TABLE_NAME
		from 
			pg_stat_user_tables
	)
	and A.schemaname = current_schema()
	and A.relid=B.objoid
	and B.objsubid=0
    and UPPER(A.relname) = UPPER(@tblName)
union all
select
--※テーブルのカラム一覧を取得のとき
    COLUMNS.ordinal_position                                      as "No."
    ,case 
       when strpos(DESCRI.description,@commentDelimiter) > 0 then
          substr(DESCRI.description
                ,strpos(DESCRI.description,@tblNameDelimiter)+char_length(@tblNameDelimiter)
                ,strpos(DESCRI.description,@commentDelimiter)-char_length(@commentDelimiter)
                )

       else
          substr(DESCRI.description
                ,strpos(DESCRI.description,@tblNameDelimiter)+char_length(@tblNameDelimiter)
          ) 
    END as 物理名
   ,trim(COLUMNS.column_name)                                     as 論理名
   ,COLUMNS.data_type                                             as タイプ
   ,COLUMNS.udt_name                                              as 型
   ,COLUMNS.character_maximum_length                              as 文字数   
   ,COLUMNS.numeric_precision                                     as 整数
   ,COLUMNS.numeric_scale                                         as 少数
   ,COLUMNS.is_nullable                                           as "NotNull"
   ,case 
    when strpos(DESCRI.description,@commentDelimiter) > 0 then
         substr(DESCRI.description
            ,strpos(DESCRI.description,@commentDelimiter)+char_length(@commentDelimiter)
             )
    else
        ''
    END as コメント
from
  pg_stat_user_tables TABLES
 ,information_schema.columns COLUMNS
 ,pg_description DESCRI
where 1=1
  and TABLES.relname=COLUMNS.table_name
  and TABLES.relid  = DESCRI.objoid 
  and COLUMNS.ordinal_position=DESCRI.objsubid
  and TABLES.schemaname = current_schema()
  and COLUMNS.table_schema = current_schema()
  and TABLES.relname = LOWER(@tblName)
order by "No."
;

テーブル・カラム調査SQL

該当カラム存在テーブル日本語検索

カラム存在テーブル日本語検索.sql


-- ==========================================
-- 機能：該当カラム存在テーブル一覧
-- 該当カラムを検索する
-- ==========================================

-------------------------------------
--*DataTitle カラム検索 [日本語]
-------------------------------------
--検索カラム名(日本語)
--*SetParameter columnNameJpn 'コード%' S
select
    trim(UTBL.schemaname)          as スキーマ名
   ,trim(SCOL.table_name)          as テーブル名
   ,SCOL.ordinal_position          as 連番
   ,trim(COLEN.column_name)        as カラム名
   ,COLJP.ColName                  as 物理名
   ,COLEN.is_nullable              as Not_NULL
   ,COLEN.data_type                as データタイプ
   ,COLEN.character_maximum_length as 最大文字列
   ,COLEN.numeric_precision        as 数値数
from
  pg_stat_user_tables UTBL
 ,information_schema.columns SCOL

 ,(select 
     substr(description,1,strpos(description,':')-1) as ColName,*
    from pg_description 
    where description like @columnNameJpn
    )COLJP

  ,(SELECT COL.* 
     from information_schema.columns COL 
     where 1=1
       and COL.table_schema = current_schema() 
    )COLEN

where 1=1
  and UTBL.relname            = SCOL.table_name
  and UTBL.relid              = COLJP.objoid 
  and SCOL.ordinal_position   = COLJP.objsubid
  and UTBL.schemaname         = SCOL.table_schema
  and COLEN.table_catalog    = SCOL.table_catalog
  and COLEN.table_name       = SCOL.table_name 
  and COLEN.ordinal_position = SCOL.ordinal_position 
  and UTBL.schemaname         = current_schema()
  and COLJP.ColName like @columnNameJpn

order by SCOL.table_name,SCOL.ordinal_position

;

該当カラム存在テーブル英語検索

カラム存在テーブル英語検索.sql


-----------------------------
--*DataTitle カラム検索[英語]
----------------------------
--検索カラム名(英語)
--*SetParameter columnNameEng 'code%' S

SELECT
     trim(A.table_schema)      as スキーマ名
    ,trim(A.column_name)       as カラム名
    ,A.ordinal_position        as カラム位置
    ,trim(A.table_name)        as テーブル名
    ,C.description             as テーブ日本語名
    ,A.is_nullable             as NotNULL
    ,A.data_type               as データタイプ
    ,A.character_maximum_length as 最大文字列
    ,A.numeric_precision        as 数値数
FROM
    information_schema.columns A
    ,pg_stat_user_tables B
    ,pg_description C
WHERE 1=1
    and A.table_schema = B.schemaname
    and A.table_name = B.relname
    and A.table_schema = current_schema()
    and A.column_name like @columnNameEng 
    and B.relid = C.objoid
    and C.objsubid=0

ORDER BY
    A.table_name
;

Select文作成SQL

A5-MK疑似命令用

A5-MK疑似命令のみ.sql

-- ==================================================
--*DataTitle カラム名取得(A5MK用)
-- ※テーブルのカラム一覧を取得し
-- A5MKの疑似命令でカラム名表示を行うCaptionsを作成
--
--【出力イメージ】
-- | -- 取得スキーマ：XXXXXXX
-- | --*Captions 会社コード,品目名,…
-- ==================================================

-- ■■■■■■■■■■■■■■■■■■■■■■■■■■
--【パラメータ：テーブル名】
--*SetParameter tblName 'TBL_XXXXX' S
--
-- ■■■■■■■■■■■■■■■■■■■■■■■■■■

select '-- 取得スキーマ：' || current_schema()                               as "-- A5MK用"
union all
select 
    concat('--*Captions ' , ARRAY_TO_STRING(ARRAY_AGG(TBL_COL.物理名),','))  as "-- A5MK用"
from (
    select
       substr(DESCRI.description,1,strpos(DESCRI.description,':')-1) as 物理名
       ,COLUMNS.ordinal_position as 連番
    from
      pg_stat_user_tables TABLES
     ,information_schema.columns COLUMNS
     ,pg_description DESCRI
    where 1=1
      and TABLES.relname=COLUMNS.table_name
      and TABLES.relid  = DESCRI.objoid 
      and COLUMNS.ordinal_position=DESCRI.objsubid
      and TABLES.schemaname = current_schema()
      and COLUMNS.table_schema = current_schema() 
      and TABLES.relname = LOWER(@tblName)
    order by COLUMNS.ordinal_position
    ) as TBL_COL
;

Select文作成[A5-MK疑似命令でカラム名].sql


-- ==================================================
-- 機能：Select文作成(A5MK用)
-- 内容:A5MKの疑似命令指定でのカラム名設定と
--      Select文を作成する
--
--【出力イメージ】
-- |-- ----------------------------
-- |--*DataTitle テーブル名
-- |-- 取得スキーマ：XXXXXXX
-- |-- ----------------------------
-- |--*Captions 会社名,品目名,更新日,…
-- |Select A.*
-- |from TBL_XXXXX　A
-- |where 1 = 1
-- |;
-- ==================================================

-- ■■■■■■■■■■■■■■■■■■■■■■■■■■
--【パラメータ：テーブル名】
--*SetParameter tblName 'TBL_XXXXX' S
--
-- ■■■■■■■■■■■■■■■■■■■■■■■■■■


select 
    '-- ----------------------------'as ";"
-- --------------------------------------
union all
select
    '--*DataTitle ' || B.description as ";"
from
	 pg_stat_user_tables A
	,pg_description B
where
	A.relname in (
		select 
			relname as TABLE_NAME
		from 
			pg_stat_user_tables
	)
	and A.schemaname = current_schema()
	and A.relid=B.objoid
	and B.objsubid=0
    and UPPER(A.relname) = UPPER(@tblName)
-- --------------------------------------
union all
select '-- 取得スキーマ：' || current_schema() as ";"
union all
select '-- ----------------------------'as ";"
union all
-- --------------------------------------
-- カラム名取得(A5MK用)
-- ------------------------------------------------------
select 
    '--*Captions ' || ARRAY_TO_STRING(ARRAY_AGG(TBL_COL.物理名),',') as ";"
from (
    select
       substr(DESCRI.description,1,strpos(DESCRI.description,':')-1) as 物理名
       ,COLUMNS.ordinal_position as 連番
    from
      pg_stat_user_tables TABLES
     ,information_schema.columns COLUMNS
     ,pg_description DESCRI
    where 1=1
      and TABLES.relname=COLUMNS.table_name
      and TABLES.relid  = DESCRI.objoid 
      and COLUMNS.ordinal_position=DESCRI.objsubid
      and TABLES.schemaname = current_schema()
      and COLUMNS.table_schema = current_schema()
      and TABLES.relname = LOWER(@tblName)
    order by COLUMNS.ordinal_position
    ) as TBL_COL
-- --------------------------------------
union all
select 'Select A.*' as ";"
-- --------------------------------------
union all
select 'from ' || @tblName || ' A' as ";"
-- --------------------------------------
union all
select 'where 1 = 1' as ";"
union all
select ';'as ";"
;

カラム名 as 句で日本語化

Select文作成[A5-MK_AS句でカラム名].sql


-- ==================================================
--*DataTitle Select文作成(カラム名)
--
-- Select文作成[カラム名込み]
-- 機能：Select文作成(カラム名)
-- 内容:カラムを「Select *」にしないで 
--      「[物理名] as [論理名]」で,Select文を作成する
--
--【出力イメージ】
-- |-- ----------------------------
-- |--*DataTitle 取得テーブル名称
-- |-- 取得スキーマ：XXXXXXX
-- |-- ----------------------------
-- |Select 
-- |   ,A.kaisya    as 会社コード
-- |   ,A.syohin    as 商品コード
-- |from T_TBL_NAME A
-- |where 1 = 1
-- ;
-- ==================================================

-- ■■■■■■■■■■■■■■■■■■■■■■■■■■
--【パラメータ：テーブル名】
--*SetParameter tblName 'TBL_XXXXX' S
--
-- ■■■■■■■■■■■■■■■■■■■■■■■■■■
select '-- ----------------------------'as  ";"
-- --------------------------------------
union all
select
    '--*DataTitle ' || B.description || '[' || @tblName || ']'  as ";"
from
	 pg_stat_user_tables A
	,pg_description B
where
	A.relname in (
		select 
			relname as TABLE_NAME
		from 
			pg_stat_user_tables
	)
	and A.schemaname = current_schema()
	and A.relid=B.objoid
	and B.objsubid=0
    and UPPER(A.relname) = UPPER(@tblName)
-- --------------------------------------
union all
select '-- 取得スキーマ：' || current_schema() as ";"
union all
select '-- ----------------------------'as  ";"
-- --------------------------------------
union all
select 'Select 'as ColName
-- --------------------------------------
union all
select A. ";"
from (
    select
      case when COLUMNS.ordinal_position = 1  then
        --【表示方法】： A.[物理名] as  [論理名]  => 「as」 を 「--」にするとコメント表示になる
        -- コメント位置を合わせるのに20文字の空白埋めをする
        '   A.' || case when length(COLUMNS.column_name) > 20  then 
                         COLUMNS.column_name 
                         else  rpad(COLUMNS.column_name,20) 
                    end  
                || ' as ' || substr(DESCRI.description,1,strpos(DESCRI.description,':')-1) 

       else
        '  ,A.' || case when length(COLUMNS.column_name) > 20  then 
                         COLUMNS.column_name 
                         else  rpad(COLUMNS.column_name,20) 
                    end  
                || ' as ' || substr(DESCRI.description,1,strpos(DESCRI.description,':')-1) 
      end as  ";"
    from
      pg_stat_user_tables TABLES
     ,information_schema.columns COLUMNS
     ,pg_description DESCRI
    where 1=1
      and TABLES.relname=COLUMNS.table_name
      and TABLES.relid  = DESCRI.objoid 
      and COLUMNS.ordinal_position=DESCRI.objsubid
      and TABLES.schemaname = current_schema()
      and COLUMNS.table_schema = current_schema()
      and TABLES.relname = LOWER(@tblName)
    order by COLUMNS.ordinal_position
)A
-- --------------------------------------
union all
select 'from ' || @tblName || ' A' as  ";"
-- --------------------------------------
union all
select 'where 1 = 1' as  ";"
union all
select ';'as ";"
;