PLSQL Tools for query primary key and foreign key of a table
来源:互联网 发布:法人的权利和义务知乎 编辑:程序博客网 时间:2024/06/02 06:37
SET VERIFY OFFSET SERVEROUTPUT ONDECLARE V_TABLE_NAME VARCHAR2(100); -- 表名 V_PK_NAME VARCHAR2(100); -- 主キー制約の名前 V_COLUMNS VARCHAR2(3000); -- 列名の取得(列1,列2,・・)BEGIN -- 表名の取得(キーボードから入力) V_TABLE_NAME := UPPER('&TABLE_NAME'); -- 主キー制約の名前の取得 SELECT CONSTRAINT_NAME INTO V_PK_NAME FROM USER_CONSTRAINTS WHERE TABLE_NAME = V_TABLE_NAME AND CONSTRAINT_TYPE = 'P'; -- 取得した主キーの列名(複合例)の取得 FOR REC IN ( SELECT COLUMN_NAME FROM USER_CONS_COLUMNS WHERE CONSTRAINT_NAME = V_PK_NAME ORDER BY POSITION ) LOOP -- "列名," を後ろに追記する V_COLUMNS := V_COLUMNS || REC.COLUMN_NAME || ','; END LOOP; -- 最後の余分な','をとっている V_COLUMNS := SUBSTR(V_COLUMNS,1,LENGTH(V_COLUMNS)-1); -- "列名,列名"の前後をかっこ()で囲む V_COLUMNS := '(' || V_COLUMNS || ')'; DBMS_OUTPUT.PUT_LINE('Table Name:' || V_TABLE_NAME || ' Primary Key :' || V_COLUMNS); -- この表を参照する子表の外部キー制約名を取得し、 -- 子表の名前と外部キー列を表示する FOR REC1 IN ( SELECT CONSTRAINT_NAME, TABLE_NAME FROM USER_CONSTRAINTS WHERE R_CONSTRAINT_NAME = V_PK_NAME AND CONSTRAINT_TYPE = 'R' ) LOOP -- さらに外部キー制約名から、列名(複合列)を変数に取得する V_COLUMNS := NULL; FOR REC2 IN ( SELECT COLUMN_NAME FROM USER_CONS_COLUMNS WHERE CONSTRAINT_NAME = REC1.CONSTRAINT_NAME ORDER BY POSITION ) LOOP V_COLUMNS := V_COLUMNS || REC2.COLUMN_NAME || ','; END LOOP; V_COLUMNS := SUBSTR(V_COLUMNS,1,LENGTH(V_COLUMNS)-1); V_COLUMNS := '(' || V_COLUMNS || ')'; -- 子表の情報を画面表示 DBMS_OUTPUT.PUT_LINE('Child Table Name:' || REC1.TABLE_NAME || ' Foreign Key :' || V_COLUMNS); END LOOP;END;
0 0
- PLSQL Tools for query primary key and foreign key of a table
- SQL Server: Get table primary key and Foreign Key using sql query
- SQL Server: Get table primary key and Foreign Key using sql query
- SQL primary key, foreign key
- Configuring a Composite primary key or Foreign Key
- Oracle get the Primary and foreign Key Column
- 有关primary key 和 foreign key
- [每日一题] OCP1z0-047 :2013-08-24 FLASHBACK—TABLE/PRIMARY KEY(FOREIGN KEY?)......98
- foreign key of Mysql
- foreign key and locks
- Nhibernate: Foreign key must have same number of columns as the referenced primary key
- Adding Primary Keys and Foreign Keys to a Table
- LOV for Editable Primary Key in ADF Table Problem
- 重建分区表主键 - Recreate Primary Key on a partition table
- SQL PRIMARY KEY 约束、SQL FOREIGN KEY 约束
- difference between primary key and candidate key
- Primary key and Unique index
- Duplicate entry '##' for key 'PRIMARY'
- js/jq解析json
- zip明文攻击
- 网络流24题 题解 (部分) 更新中
- Shutting down interface eth0: Error: Device 'eth0' (/org/freedesktop/NetworkManager/Devices/0) disc
- 获取Android签名内容
- PLSQL Tools for query primary key and foreign key of a table
- PAT A1065. A+B and C (64bit) (20)
- socket 通信代码,单线程
- js跨域
- jvm1-深入浅出Java垃圾回收机制
- Intel Galileo Debian Image Prequits
- Oracle数据库开发利器之函数
- 运行环信demo遇到的相关问题
- oracle 分库分表(sharding)