1.查询用户的所有表
2.查询表关系，外键，主键,约束条件
这些技术是在开发BI项目时使用到的，其实SQLServer或者PL/SQL都已经使用这些东西了
=========================================================
oracle数据库：
String reSql = "SELECT A.TABLE_NAME AS P_TABLE_NAME,B.COLUMN_NAME AS P_COLUMN_NAME,C.TABLE_NAME AS F_TABLE_NAME,"+
"D.COLUMN_NAME AS F_COLUMN_NAME FROM USER_CONSTRAINTS A LEFT JOIN USER_CONS_COLUMNS B ON A.CONSTRAINT_NAME=B.CONSTRAINT_NAME "+
"LEFT JOIN USER_CONSTRAINTS C ON C.R_CONSTRAINT_NAME = A.CONSTRAINT_NAME LEFT JOIN USER_CONS_COLUMNS D ON C.CONSTRAINT_NAME= "+
"D.CONSTRAINT_NAME WHERE A.CONSTRAINT_TYPE='P' AND NVL(C.TABLE_NAME,' ')!=' ' AND NVL(D.COLUMN_NAME,' ')!=' ' ORDER BY A.TABLE_NAME";
String rsSql = "SELECT COL.COLUMN_NAME FROM USER_CONSTRAINTS CON,USER_CONS_COLUMNS COL "
+ "WHERE CON.CONSTRAINT_NAME = COL.CONSTRAINT_NAME AND CON.CONSTRAINT_TYPE='P' AND COL.TABLE_NAME='"
+ tableName + "' ORDER BY COLUMN_NAME";
String sql = "(select TABLE_NAME AS name,'U' as xtype from ALL_TABLES where OWNER IN ('DBO','VHBIYF') "+
"union all "+
"select VIEW_NAME AS name,'V' as xtype from all_views where owner IN ('DBO','VHBIYF') ) result";
return sql;
=========================================================
sqlserver：
String rsSql = "SELECT C.NAME AS COLUMN_NAME FROM SYSINDEXES I JOIN SYSINDEXKEYS K ON I.ID=K.ID AND I.INDID=K.INDID "
+ "JOIN SYSOBJECTS O ON I.ID = O.ID JOIN SYSCOLUMNS C ON I.ID=C.ID AND K.COLID=C.COLID "
+ "WHERE O.XTYPE='U' AND EXISTS(SELECT 1 FROM SYSOBJECTS WHERE XTYPE='PK' AND NAME=I.NAME) "
+ "AND O.NAME='"+tableName+"' ORDER BY O.NAME,K.COLID";
String reSql="select oMain.name AS P_TABLE_NAME,MainCol.name AS P_COLUMN_NAME,oSub.name AS F_TABLE_NAME,SubCol.name AS F_COLUMN_NAME "+
"from sys.foreign_keys fk JOIN sys.all_objects oSub ON (fk.parent_object_id = oSub.object_id) JOIN sys.all_objects oMain "+
"ON (fk.referenced_object_id = oMain.object_id) JOIN sys.foreign_key_columns fkCols ON (fk.object_id = fkCols.constraint_object_id) "+
"JOIN sys.columns SubCol ON (oSub.object_id = SubCol.object_id AND fkCols.parent_column_id = SubCol.column_id) JOIN sys.columns MainCol "+
"ON (oMain.object_id = MainCol.object_id AND fkCols.referenced_column_id = MainCol.column_id)";
=========================================================
/************************************************************
* Code formatted by SoftTree SQL Assistant ?v6.5.278
* Time: 2014/9/12 15:07:12
************************************************************/
SELECT DIM_375.id AS DIM_375_CODE,
MAX(DIM_375.province) AS DIM_375_DISPLAY,
DIM_370.id AS DIM_370_CODE,
MAX(DIM_370.categry_name) AS DIM_370_DISPLAY,
DIM_371.id AS DIM_371_CODE,
MAX(DIM_371.proname) AS DIM_371_DISPLAY,
'201406' AS DIM_DATE_CODE,
'YEAR_MONTH' AS DIM_DATE_LEVEL,
DIM_126.comp_code AS DIM_126_CODE,
MAX(DIM_126.comp_name) AS DIM_126_DISPLAY,
SUM(DIM_373.seals_money) AS DIM_373_DATA
FROM (
SELECT *
FROM yyb_sales yyb_sales WITH (NOLOCK)
) DIM_373
INNER JOIN (
SELECT *
FROM yyb_customer yyb_customer WITH (NOLOCK)
) DIM_374
ON (1 = 1 AND DIM_374.id = DIM_373.customer_id)
INNER JOIN (
SELECT *
FROM yyb_region yyb_region WITH (NOLOCK)
) DIM_375
ON (1 = 1 AND DIM_375.id = DIM_374.address)
INNER JOIN (
SELECT *
FROM yyb_productor yyb_productor WITH (NOLOCK)
) DIM_371
ON (1 = 1 AND DIM_371.id = DIM_373.productor_id)
INNER JOIN (
SELECT *
FROM yyb_categry yyb_categry WITH (NOLOCK)
) DIM_370
ON (1 = 1 AND DIM_370.id = DIM_371.categry_id)
INNER JOIN (
SELECT *
FROM sys_company sys_company WITH (NOLOCK)
WHERE sys_company.is_stop = 0
) DIM_126
ON (1 = 1 AND DIM_126.comp_code = DIM_373.comp_code)
WHERE DIM_373.year = '2014'
AND DIM_373.month = '06'
AND DIM_126.comp_code IN ('1000')
GROUP BY
DIM_375.id,
DIM_370.id,
DIM_371.id,
DIM_126.comp_code
ORDER BY
DIM_375.id ASC,
DIM_370.id ASC,
DIM_371.id ASC,
DIM_126.comp_code ASC
=========================================
SELECT DIM_27_CODE as DIM_27_CODE,
MAX(DIM_27_DISPLAY) as DIM_27_DISPLAY,
DIM_29_CODE as DIM_29_CODE,
MAX(DIM_29_DISPLAY) as DIM_29_DISPLAY,
DIM_DATE_CODE as DIM_DATE_LAST_CODE,
SUM(DIM_30_DATA) as DIM_30_DATA
FROM vhbiyf.rep_temp_report_22
WHERE DIM_DATE_CODE IN ('201409',
'201301',
'201302',
'201303',
'201304',
'201305',
'201306',
'201307',
'201308',
'201309',
'201310',
'201311',
'201312')
AND DIM_DATE_LEVEL = 'YEAR_MONTH'
AND DIM_21_CODE IN ('1000')
AND DIM_27_CODE IN ('1', '3')
GROUP BY DIM_27_CODE, DIM_29_CODE, DIM_DATE_CODE
ORDER BY DIM_27_CODE ASC, DIM_29_CODE ASC
==================================
/************************************************************
* Code formatted by SoftTree SQL Assistant ?v6.5.278
* Time: 2014/9/24 13:24:11
************************************************************/
CREATE TABLE temp_DIM_30
(
DIM_30_fint_year VARCHAR(4000),
DIM_30_fint_month VARCHAR(4000),
DIM_30_a_id VARCHAR(4000),
DIM_30_p_id VARCHAR(4000),
DIM_30_comp_code VARCHAR(4000),
DIM_30_u_id NUMBER(18, 6)
);
CREATE TABLE temp_DIM_27
(
DIM_30_fint_year VARCHAR(4000),
DIM_30_fint_month VARCHAR(4000),
DIM_30_a_id VARCHAR(4000),
DIM_30_p_id VARCHAR(4000),
DIM_30_comp_code VARCHAR(4000),
DIM_27_name VARCHAR(4000),
DIM_27_id VARCHAR(4000),
DIM_30_u_id NUMBER(18, 6)
);
CREATE TABLE temp_DIM_29
(
DIM_30_fint_year VARCHAR(4000),
DIM_30_fint_month VARCHAR(4000),
DIM_30_a_id VARCHAR(4000),
DIM_30_p_id VARCHAR(4000),
DIM_30_comp_code VARCHAR(4000),
DIM_27_name VARCHAR(4000),
DIM_27_id VARCHAR(4000),
DIM_29_name VARCHAR(4000),
DIM_29_id VARCHAR(4000),
DIM_30_u_id NUMBER(18, 6)
);
CREATE TABLE temp_DIM_21
(
DIM_30_fint_year VARCHAR(4000),
DIM_30_fint_month VARCHAR(4000),
DIM_30_a_id VARCHAR(4000),
DIM_30_p_id VARCHAR(4000),
DIM_30_comp_code VARCHAR(4000),
DIM_27_name VARCHAR(4000),
DIM_27_id VARCHAR(4000),
DIM_29_name VARCHAR(4000),
DIM_29_id VARCHAR(4000),
DIM_21_comp_name VARCHAR(4000),
DIM_21_comp_code VARCHAR(4000),
DIM_30_u_id NUMBER(18, 6)
);
INSERT INTO temp_DIM_30
SELECT DIM_30.fint_year AS DIM_30_fint_year,
DIM_30.fint_month AS DIM_30_fint_month,
DIM_30.a_id AS DIM_30_a_id,
DIM_30.p_id AS DIM_30_p_id,
DIM_30.comp_code AS DIM_30_comp_code,
COUNT(DIM_30.u_id) AS DIM_30_u_id
FROM (
SELECT YYB_ORDER.*
FROM YYB_ORDER YYB_ORDER
) DIM_30
WHERE DIM_30.fint_year = '2014'
AND DIM_30.fint_month = '09'
GROUP BY
DIM_30.fint_year,
DIM_30.fint_month,
DIM_30.a_id,
DIM_30.p_id,
DIM_30.comp_code;
INSERT INTO temp_DIM_27
SELECT DIM_30.DIM_30_fint_year AS DIM_30_fint_year,
DIM_30.DIM_30_fint_month AS DIM_30_fint_month,
DIM_30.DIM_30_a_id AS DIM_30_a_id,
DIM_30.DIM_30_p_id AS DIM_30_p_id,
DIM_30.DIM_30_comp_code AS DIM_30_comp_code,
DIM_27.name AS DIM_27_name,
DIM_27.id AS DIM_27_id,
SUM(DIM_30.DIM_30_u_id) AS DIM_30_u_id
FROM temp_DIM_30 DIM_30
INNER JOIN (
SELECT YYB_AREA.*
FROM YYB_AREA YYB_AREA
) DIM_27
ON (1 = 1 AND DIM_27.id = DIM_30.DIM_30_a_id)
GROUP BY
DIM_30.DIM_30_fint_year,
DIM_30.DIM_30_fint_month,
DIM_30.DIM_30_a_id,
DIM_30.DIM_30_p_id,
DIM_30.DIM_30_comp_code,
DIM_27.name,
DIM_27.id;
INSERT INTO temp_DIM_29
SELECT DIM_27.DIM_30_fint_year AS DIM_30_fint_year,
DIM_27.DIM_30_fint_month AS DIM_30_fint_month,
DIM_27.DIM_30_a_id AS DIM_30_a_id,
DIM_27.DIM_30_p_id AS DIM_30_p_id,
DIM_27.DIM_30_comp_code AS DIM_30_comp_code,
DIM_27.DIM_27_name AS DIM_27_name,
DIM_27.DIM_27_id AS DIM_27_id,
DIM_29.name AS DIM_29_name,
DIM_29.id AS DIM_29_id,
SUM(DIM_27.DIM_30_u_id) AS DIM_30_u_id
FROM temp_DIM_27 DIM_27
INNER JOIN (
SELECT YYB_PRO.*
FROM YYB_PRO YYB_PRO
) DIM_29
ON (1 = 1 AND DIM_29.id = DIM_27.DIM_30_p_id)
GROUP BY
DIM_27.DIM_30_fint_year,
DIM_27.DIM_30_fint_month,
DIM_27.DIM_30_a_id,
DIM_27.DIM_30_p_id,
DIM_27.DIM_30_comp_code,
DIM_27.DIM_27_name,
DIM_27.DIM_27_id,
DIM_29.name,
DIM_29.id;
INSERT INTO temp_DIM_21
SELECT DIM_29.DIM_30_fint_year AS DIM_30_fint_year,
DIM_29.DIM_30_fint_month AS DIM_30_fint_month,
DIM_29.DIM_30_a_id AS DIM_30_a_id,
DIM_29.DIM_30_p_id AS DIM_30_p_id,
DIM_29.DIM_30_comp_code AS DIM_30_comp_code,
DIM_29.DIM_27_name AS DIM_27_name,
DIM_29.DIM_27_id AS DIM_27_id,
DIM_29.DIM_29_name AS DIM_29_name,
DIM_29.DIM_29_id AS DIM_29_id,
DIM_21.comp_name AS DIM_21_comp_name,
DIM_21.comp_code AS DIM_21_comp_code,
SUM(DIM_29.DIM_30_u_id) AS DIM_30_u_id
FROM temp_DIM_29 DIM_29
INNER JOIN (
SELECT SYS_COMPANY.*
FROM SYS_COMPANY SYS_COMPANY
) DIM_21
ON (1 = 1 AND DIM_21.comp_code = DIM_29.DIM_30_comp_code)
WHERE DIM_21.comp_code IN ('1000')
GROUP BY
DIM_29.DIM_30_fint_year,
DIM_29.DIM_30_fint_month,
DIM_29.DIM_30_a_id,
DIM_29.DIM_30_p_id,
DIM_29.DIM_30_comp_code,
DIM_29.DIM_27_name,
DIM_29.DIM_27_id,
DIM_29.DIM_29_name,
DIM_29.DIM_29_id,
DIM_21.comp_name,
DIM_21.comp_code;
SELECT DIM_21.DIM_27_id AS DIM_27_CODE,
MAX(DIM_21.DIM_27_name) AS DIM_27_DISPLAY,
DIM_21.DIM_29_id AS DIM_29_CODE,
MAX(DIM_21.DIM_29_name) AS DIM_29_DISPLAY,
'201409' AS DIM_DATE_CODE,
'YEAR_MONTH' AS DIM_DATE_LEVEL,
DIM_21.DIM_21_comp_code AS DIM_21_CODE,
MAX(DIM_21.DIM_21_comp_name) AS DIM_21_DISPLAY,
SUM(DIM_21.DIM_30_u_id) AS DIM_30_DATA
FROM temp_DIM_21 DIM_21
GROUP BY
DIM_21.DIM_27_id,
DIM_21.DIM_29_id,
DIM_21.DIM_21_comp_code
ORDER BY
DIM_21.DIM_27_id ASC,
DIM_21.DIM_29_id ASC,
DIM_21.DIM_21_comp_code ASC;DROP TABLE temp_DIM_30;DROP TABLE temp_DIM_27;
DROP TABLE temp_DIM_29;DROP TABLE temp_DIM_21;
==============================
