sql: postgreSQL sql script

標籤：

SELECT * from pg_class c,pg_attribute a,pg_type t where c.relname=‘BookKindList‘ and a.attnum>0 and a.attrelid=c.oid and a.atttypid=t.oidSELECT a.attname from pg_class c,pg_attribute a,pg_type t where c.relname=‘BookKindList‘ and a.attnum>0 and a.attrelid=c.oid and a.atttypid=t.oid--查詢BookKindList表的欄位資訊 20150402 Geovin Du 塗聚文SELECT a.attnum,a.attname AS field,t.typname AS type,a.attlen AS length,a.atttypmod AS lengthvar,a.attnotnull AS notnull from pg_class c,pg_attribute a,pg_type t where c.relname=‘BookKindList‘ and a.attnum>0 and a.attrelid=c.oid and a.atttypid=t.oidSELECT * FROM information_schema.columns; --查表的列表SELECT * FROM information_schema.columns where table_catalog=‘geovindu‘ and table_schema=‘public‘ and table_name=‘bookkindlist‘;--select * from pg_database where datname=‘bookkindlist‘;select datname,dattablespace from pg_database where datname=‘bookkindlist‘;--查看資料庫select * from pg_database;--查看錶空間select * from pg_tablespace; --查看語言select * from pg_language; --查看角色使用者select * from pg_user;select * from pg_shadow;select * from pg_roles; --查看會話進程select * from pg_stat_activity; --查看錶SELECT * FROM pg_tables where schemaname = ‘public‘; --查看錶欄位select * from information_schema.columns where table_schema = ‘public‘ and table_name = ‘bookkindlist‘; --查看視圖select * from pg_views where schemaname = ‘public‘;select * from information_schema.views where table_schema = ‘public‘; --查看觸發器select * from information_schema.triggers; --查看序列select * from information_schema.sequences where sequence_schema = ‘public‘;  --查看約束select * from pg_constraint where contype = ‘p‘  --u unique,p primary,f foreign,c check,t trigger,x exclusion select a.relname as table_name,b.conname as constraint_name,b.contype as constraint_type from pg_class a,pg_constraint b where a.oid = b.conrelid and a.relname = ‘bookkindlist‘; --查看索引select * from pg_index ; --查看錶上存在哪些索引以及大小select relname,n.amname as index_type from pg_class m,pg_am n where m.relam = n.oid and m.oid in (select b.indexrelid from pg_class a,pg_index b where a.oid = b.indrelid and a.relname = ‘bookkindlist‘); SELECT c.relname,c2.relname, c2.relpages*8 as size_kbFROM pg_class c, pg_class c2, pg_index iWHERE c.relname = ‘bookkindlist‘ ANDc.oid = i.indrelid ANDc2.oid = i.indexrelidORDER BY c2.relname;  --查看索引定義select b.indexrelid from pg_class a,pg_index b where a.oid = b.indrelid and a.relname = ‘bookkindlist‘;select pg_get_indexdef(b.indexrelid); --查看過程函數定義select oid,* from pg_proc where proname = ‘insert_platform_action_exist‘; --oid = 24610select * from pg_get_functiondef(24610); --查看錶大小(不含索引等資訊)select pg_relation_size(‘bookkindlist‘);                         --368640 byteselect pg_size_pretty(pg_relation_size(‘bookkindlist‘))   --360 kB --查看DB大小select pg_size_pretty(pg_database_size(‘geovindu‘));   --12M --查看伺服器DB運行狀態[[email protected] ~]$ pg_ctl status -D $PGDATApg_ctl: server is running (PID: 2373)/home/postgres/bin/postgres "-D" "/database/pgdata"  --查看每個DB的使用方式(讀,寫,緩衝,更新,事務等)select * from pg_stat_database --查看索引的使用方式select * from pg_stat_user_indexes; --查看錶所對應的資料檔案路徑與大小SELECT pg_relation_filepath(oid), relpages FROM pg_class WHERE relname = ‘bookkindlist‘; --查看索引與相關欄位及大小 SELECT n.nspname AS schema_name,        r.rolname as table_owner,       bc.relname AS table_name,       ic.relname AS index_name,       a.attname  AS column_name,       bc.relpages*8 as index_size_kb       FROM pg_namespace n,       pg_class bc,             -- base class       pg_class ic,             -- index class       pg_index i,       pg_attribute a,           -- att in base       pg_roles r  WHERE bc.relnamespace = n.oid     and i.indrelid = bc.oid     and i.indexrelid = ic.oid     and bc.relowner = r.oid     and i.indkey[0] = a.attnum     and i.indnatts = 1     and a.attrelid = bc.oid     and n.nspname = ‘public‘     and bc.relname = ‘bookkindlist‘  ORDER BY schema_name, table_name, index_name, attname; --查看PG鎖select * from pg_locks; 備忘：relpages*8 是實際所佔磁碟大小 --查看錶空間大小select pg_tablespace_size(‘pg_default‘); --查看序列與表的對應關係  WITH fq_objects AS (SELECT c.oid,c.relname AS fqname ,                           c.relkind, c.relname AS relation                    FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace ),      sequences AS (SELECT oid,fqname FROM fq_objects WHERE relkind = ‘S‘),      tables    AS (SELECT oid, fqname FROM fq_objects WHERE relkind = ‘r‘ )          SELECT       s.fqname AS sequence,       ‘->‘ as depends,       t.fqname AS table      FROM       pg_depend d JOIN sequences s ON s.oid = d.objid                  JOIN tables t ON t.oid = d.refobjid           WHERE       d.deptype = ‘a‘ and t.fqname = ‘bookkindlist‘;--select * from information_schema.columns where table_catalog= ‘geovindu‘ AND table_name = ‘bookkindlist‘;select * from pg_description;  ---一個查詢表結構的ＳＱＬ  SELECT    col.table_schema ,    col.table_name ,    col.ordinal_position,    col.column_name ,    col.data_type ,    col.character_maximum_length,    col.numeric_precision,    col.numeric_scale,    col.is_nullable,    col.column_default ,    des.descriptionFROM    information_schema.columns col LEFT JOIN pg_description des        ON col.table_name::regclass = des.objoid    AND col.ordinal_position = des.objsubidWHERE    table_schema = ‘public‘    AND table_name = ‘bookkindlist‘ORDER BY    ordinal_position;select * from pg_namespaceselect * from pg_class where relname=‘bookkindlist‘---SELECTn.nspname ,relnameFROMpg_class c ,pg_namespace nWHEREc.relnamespace = n.oidAND nspname=‘public‘AND relkind = ‘r‘AND relhassubclassORDER BYnspname ,relname;--select * from information_schema.columns where table_name = ‘bookkindlist‘;--表結構select table_schema,table_name,column_name,data_type,column_default,character_maximum_length,is_nullable from information_schema.columns where table_name = ‘bookkindlist‘;select table_schema,table_name,column_name as FieldName,data_type as   FieldType,column_default,character_maximum_length as   FieldLength,is_nullable from information_schema.columns where table_name = ‘bookkindlist‘;--表主鍵名稱select pg_constraint.conname as pk_name from pg_constraint  inner join pg_class  on pg_constraint.conrelid = pg_class.oid where pg_class.relname = ‘bookkindlist‘ and pg_constraint.contype=‘p‘;--表主鍵欄位select pg_constraint.conname as pk_name,pg_attribute.attname as column_name,pg_type.typname as data_type,pg_class.relname as table_name, info.character_maximum_length,info.is_nullable  from pg_constraint  inner join pg_class on pg_constraint.conrelid = pg_class.oid inner join pg_attribute on pg_attribute.attrelid = pg_class.oid and  pg_attribute.attnum = pg_constraint.conkey[1]inner join pg_type on pg_type.oid = pg_attribute.atttypidinner join information_schema.columns as info on info.column_name=pg_attribute.attname where pg_class.relname = ‘bookkindlist‘ and pg_constraint.contype=‘p‘;--表主鍵名稱select conname,conrelid,connamespace from pg_constraint where contype=‘p‘;select * from pg_constraint where contype=‘p‘;---表和表主鍵名稱select oid,relname from pg_class;select * from pg_class where relnamespace=2200;select * from pg_class;--表select * from pg_type where typnamespace=2200;select typnamespace,typname,oid from pg_type where typnamespace=2200;select * from pg_type where typname=‘bookkindlist‘;-- 主鍵欄位名attnameselect * from pg_attribute;select * from pg_attribute where attstorage=‘p‘;select attrelid,attname,attnum from pg_attribute where attstorage=‘p‘;

　　

sql: postgreSQL sql script