初めに
こちらの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 ";"
;