?? sql_text.sql
字號:
################################################## Build 2
##################################################
################################################## INFORMIX ###########
##################################################
##################################################
#INFORMIX.ServerItems
/*SELECT
user OBJ_NAME,
user TEMPLATE_NAME,
0 SUB_OBJ_NAME,
user NODES_SQLCLASS_NAME,
user LOCAL_FILTER
,user AS aowner
FROM systables s
WHERE 1 <> 1 --Tables
UNION ALL*/
SELECT
trim(both from CAST("Tables " AS VARCHAR(100))) OBJ_NAME,
trim(both from CAST("Tables " AS VARCHAR(100))) TEMPLATE_NAME,
count(*) SUB_OBJ_NAME,
CAST("TableObjects" AS VARCHAR(20)) NODES_SQLCLASS_NAME,
CAST("atype = 'T' and owner <> 'informix'" AS VARCHAR(100)) LOCAL_FILTER
,trim(both from user) AS aowner
,0 Image_Index
FROM systables s
WHERE tabtype = 'T' and owner <> 'informix' --Tables
UNION ALL
SELECT
CAST("Views" AS VARCHAR(20)) OBJ_NAME,
CAST("Views" AS VARCHAR(20)) TEMPLATE_NAME,
count(*) SUB_OBJ_NAME,
CAST("TableObjects" AS VARCHAR(20)) NODES_SQLCLASS_NAME,
CAST("atype = 'V' and owner <> 'informix'" AS VARCHAR(100)) LOCAL_FILTER
,user AS aowner
,4 Image_Index
FROM systables s --Views
WHERE tabtype = 'V' and owner <> 'informix'
UNION ALL
SELECT
CAST("Sinonims" AS VARCHAR(20)) OBJ_NAME,
CAST("Sinonims" AS VARCHAR(20)) TEMPLATE_NAME,
count(*) SUB_OBJ_NAME,
CAST("TableObjects" AS VARCHAR(20)) NODES_SQLCLASS_NAME,
CAST("atype IN ('P', 'S') and owner <> 'informix'" AS VARCHAR(100)) LOCAL_FILTER
,user AS aowner
,4 Image_Index
FROM systables s --Sinonims
WHERE tabtype IN ('P', 'S') and owner <> 'informix'
UNION ALL
SELECT
CAST("Procedures" AS VARCHAR(20)) OBJ_NAME,
CAST("Procedures" AS VARCHAR(20)) TEMPLATE_NAME,
count(*) SUB_OBJ_NAME,
CAST("TableObjects" AS VARCHAR(20)) NODES_SQLCLASS_NAME,
CAST("atype = 'PR' and owner <> 'informix'" AS VARCHAR(100)) LOCAL_FILTER
,user AS aowner
,3 Image_Index
FROM systables s --Synonyms
WHERE tabtype = 'PR' and owner <> 'informix'
UNION ALL
SELECT
CAST("SysInfo" AS VARCHAR(20)) OBJ_NAME,
CAST("SysInfo" AS VARCHAR(20)) TEMPLATE_NAME,
count(*) SUB_OBJ_NAME,
CAST("SysInfo" AS VARCHAR(20)) NODES_SQLCLASS_NAME,
CAST("owner = 'informix'" AS VARCHAR(100)) LOCAL_FILTER
,user AS aowner
,-1 Image_Index
FROM systables s --SysInfo
WHERE 1<>1
##################################################
#INFORMIX.Tables
select
trim(both from owner) || '.' || trim(both from tabname) objname,
s.tabid,
case when s.owner = 'informix' then 1 else 0 end order1,
trim(both from owner) owner,
s.tabname name,
trim(s.tabtype || " ") atype
, "TableColumns" NODES_SQLCLASS_NAME
, "TableColumns" TEMPLATE_NAME
, CAST(("tabid = '" || s.tabid || "'") AS VARCHAR(100)) LOCAL_FILTER
, 0 Image_Index
from systables s
where tabtype <> "" and owner <> 'informix'
order by 1,2,3
##################################################
#INFORMIX.Tables.Columns
SELECT
colname,
tabid,
colno,
collength as Length,
colmin,
colmax,
coltype-trunc(coltype/256)*256 as Type,
case when coltype-trunc(coltype/256)*256=0 then "CHAR"
when coltype-trunc(coltype/256)*256=1 then "SMALLINT"
when coltype-trunc(coltype/256)*256=2 then "INTEGER"
when coltype-trunc(coltype/256)*256=3 then "FLOAT"
when coltype-trunc(coltype/256)*256=4 then "SMALLFLOAT"
when coltype-trunc(coltype/256)*256=5 then "DECIMAL"
when coltype-trunc(coltype/256)*256=6 then "SERIAL*"
when coltype-trunc(coltype/256)*256=7 then "DATE"
when coltype-trunc(coltype/256)*256=8 then "MONEY"
when coltype-trunc(coltype/256)*256=9 then "NULL"
when coltype-trunc(coltype/256)*256=10 then "DATETIME"
when coltype-trunc(coltype/256)*256=11 then "BYTE"
when coltype-trunc(coltype/256)*256=12 then "TEXT"
when coltype-trunc(coltype/256)*256=13 then "VARCHAR"
when coltype-trunc(coltype/256)*256=14 then "INTERVAL"
when coltype-trunc(coltype/256)*256=15 then "NCHAR"
when coltype-trunc(coltype/256)*256=16 then "NVARCHAR"
when coltype-trunc(coltype/256)*256=17 then "INT8"
when coltype-trunc(coltype/256)*256=18 then "SERIAL8*"
when coltype-trunc(coltype/256)*256=19 then "SET"
when coltype-trunc(coltype/256)*256=20 then "MULTISET"
when coltype-trunc(coltype/256)*256=21 then "LIST"
when coltype-trunc(coltype/256)*256=22 then "ROW"
when coltype-trunc(coltype/256)*256=23 then "COLLECTION"
when coltype-trunc(coltype/256)*256=24 then "ROWREF"
when coltype-trunc(coltype/256)*256=40 then "Variable-length opaque type"
when coltype-trunc(coltype/256)*256=41 then "Fixed-length opaque type"
when coltype-trunc(coltype/256)*256=4118 then "Named row"
end as TypeName
, trunc(coltype/256) as Null
, "" TEMPLATE_NAME
, case when trunc(coltype/256)=0 then "Yes" else "No" end as CanNull
, 1 Image_Index
FROM informix.syscolumns
order by tabid, colno
##################################################
#INFORMIX.Procedures
select
trim(both from owner) || '.' || trim(both from procname) objname,
case when s.owner = 'informix' then 1 else 0 end order1,
trim(both from owner) owner,
s.procname name
, "ProceduresParameters" NODES_SQLCLASS_NAME
, "ProceduresParameters" TEMPLATE_NAME
, CAST(("") AS VARCHAR(100)) LOCAL_FILTER
, 3 Image_Index
from sysprocedures s
where owner <> 'informix'
order by 1,2,3
##################################################
#INFORMIX.Procedure.Body
select procid, datakey, seqno, data
from sysprocbody
where procid = 563
and datakey = 'T'
order by seqno
##################################################
#INFORMIX.SysInfo.ServerItems
SELECT
CAST("SysTables" AS VARCHAR(100)) OBJ_NAME,
CAST("SysTables" AS VARCHAR(100)) TEMPLATE_NAME,
count(*) SUB_OBJ_NAME,
CAST("SysTableObjects" AS VARCHAR(20)) NODES_SQLCLASS_NAME,
CAST("atype = 'T' and owner = 'informix'" AS VARCHAR(100)) LOCAL_FILTER,
CAST("informix" AS VARCHAR(20)) aowner
, 0 Image_Index
FROM systables s
WHERE tabtype = 'T' and owner = 'informix' --Tables
UNION ALL
SELECT
CAST("SysViews" AS VARCHAR(20)) OBJ_NAME,
CAST("SysViews" AS VARCHAR(20)) TEMPLATE_NAME,
count(*) SUB_OBJ_NAME,
CAST("SysTableObjects" AS VARCHAR(20)) NODES_SQLCLASS_NAME,
CAST("atype = 'V' and owner = 'informix'" AS VARCHAR(100)) LOCAL_FILTER,
CAST("informix" AS VARCHAR(20)) aowner
, 4 Image_Index
FROM systables s --Views
WHERE tabtype = 'V' and owner = 'informix'
UNION ALL
SELECT
CAST("SysSinonims" AS VARCHAR(20)) OBJ_NAME,
CAST("SysSinonims" AS VARCHAR(20)) TEMPLATE_NAME,
count(*) SUB_OBJ_NAME,
CAST("SysSinonims" AS VARCHAR(20)) NODES_SQLCLASS_NAME,
CAST("atype IN ('P', 'S') and owner = 'informix'" AS VARCHAR(100)) LOCAL_FILTER,
CAST("informix" AS VARCHAR(20)) aowner
, 4 Image_Index
FROM systables s --Sinonims
WHERE tabtype IN ('P', 'S') and owner = 'informix'
UNION ALL
SELECT
CAST("SysProcedures" AS VARCHAR(20)) OBJ_NAME,
CAST("SysProcedures" AS VARCHAR(20)) TEMPLATE_NAME,
count(*) SUB_OBJ_NAME,
CAST("SysProcedures" AS VARCHAR(20)) NODES_SQLCLASS_NAME,
CAST("atype = 'PR' and owner = 'informix'" AS VARCHAR(100)) LOCAL_FILTER,
CAST("informix" AS VARCHAR(20)) aowner
, 3 Image_Index
FROM systables s --Synonyms
WHERE tabtype = 'PR' and owner = 'informix'
##################################################
#INFORMIX.SysTables
select
trim(both from owner) || '.' || trim(both from tabname) objname,
s.tabid,
case when s.owner = 'informix' then 1 else 0 end order1,
trim(both from owner) owner,
s.tabname name,
trim(s.tabtype || " ") atype
, "TableColumns" NODES_SQLCLASS_NAME
, "TableColumns" TEMPLATE_NAME
, CAST(("tabid = '" || s.tabid || "'") AS VARCHAR(100)) LOCAL_FILTER
, 0 Image_Index
from systables s
where tabtype <> "" and owner = 'informix'
order by 1,2,3
##################################################
#INFORMIX.SysTables.Columns
SELECT
colname,
tabid,
colno,
collength as Length,
colmin,
colmax,
coltype-trunc(coltype/256)*256 as Type,
case when coltype-trunc(coltype/256)*256=0 then "CHAR"
when coltype-trunc(coltype/256)*256=1 then "SMALLINT"
when coltype-trunc(coltype/256)*256=2 then "INTEGER"
when coltype-trunc(coltype/256)*256=3 then "FLOAT"
when coltype-trunc(coltype/256)*256=4 then "SMALLFLOAT"
when coltype-trunc(coltype/256)*256=5 then "DECIMAL"
when coltype-trunc(coltype/256)*256=6 then "SERIAL*"
when coltype-trunc(coltype/256)*256=7 then "DATE"
when coltype-trunc(coltype/256)*256=8 then "MONEY"
when coltype-trunc(coltype/256)*256=9 then "NULL"
when coltype-trunc(coltype/256)*256=10 then "DATETIME"
when coltype-trunc(coltype/256)*256=11 then "BYTE"
when coltype-trunc(coltype/256)*256=12 then "TEXT"
when coltype-trunc(coltype/256)*256=13 then "VARCHAR"
when coltype-trunc(coltype/256)*256=14 then "INTERVAL"
when coltype-trunc(coltype/256)*256=15 then "NCHAR"
when coltype-trunc(coltype/256)*256=16 then "NVARCHAR"
when coltype-trunc(coltype/256)*256=17 then "INT8"
when coltype-trunc(coltype/256)*256=18 then "SERIAL8*"
when coltype-trunc(coltype/256)*256=19 then "SET"
when coltype-trunc(coltype/256)*256=20 then "MULTISET"
when coltype-trunc(coltype/256)*256=21 then "LIST"
when coltype-trunc(coltype/256)*256=22 then "ROW"
when coltype-trunc(coltype/256)*256=23 then "COLLECTION"
when coltype-trunc(coltype/256)*256=24 then "ROWREF"
when coltype-trunc(coltype/256)*256=40 then "Variable-length opaque type"
when coltype-trunc(coltype/256)*256=41 then "Fixed-length opaque type"
when coltype-trunc(coltype/256)*256=4118 then "Named row"
end as TypeName,
trunc(coltype/256) as Null,
"" TEMPLATE_NAME,
case when trunc(coltype/256)=0 then "Yes" else "No" end as CanNull
, 1 Image_Index
FROM informix.syscolumns
order by tabid, colno
##################################################
#INFORMIX.SysProcedures
select
trim(both from owner) || '.' || trim(both from procname) objname,
case when s.owner = 'informix' then 1 else 0 end order1,
trim(both from owner) owner,
s.procname name
, "ProceduresParameters" NODES_SQLCLASS_NAME
, "ProceduresParameters" TEMPLATE_NAME
, CAST(("") AS VARCHAR(100)) LOCAL_FILTER
, 3 Image_Index
from sysprocedures s
where owner = 'informix'
order by 1,2,3
##################################################
#INFORMIX.Procedure.Body
select procid, datakey, seqno, data
from sysprocbody
where procid = 563
and datakey = 'T'
order by seqno
##################################################
##################################################
################################################## ORACLE #############
##################################################
##################################################
#ORACLE.ServerItems
--Tables
SELECT
CAST('Tables' AS VARCHAR2(20)) OBJ_NAME,
CAST('Tables' AS VARCHAR2(20)) TEMPLATE_NAME,
count(*) SUB_OBJ_NAME,
CAST('TableObjects' AS VARCHAR2(20)) NODES_SQLCLASS_NAME,
CAST(' atype = ''T'' and owner <> ''SYS'' ' AS VARCHAR2(200) ) LOCAL_FILTER
,0 Image_Index
FROM SYS.ALL_TABLES A
WHERE A.OWNER NOT IN ('SYS', 'SYSTEM')
-- Views
UNION ALL
SELECT
CAST('Views' AS VARCHAR2(20)) OBJ_NAME,
CAST('Views' AS VARCHAR2(20)) TEMPLATE_NAME,
count(*) SUB_OBJ_NAME,
CAST('TableObjects' AS VARCHAR2(20)) NODES_SQLCLASS_NAME,
CAST('atype = ''V'' and owner <> ''informix''' AS VARCHAR2(100)) LOCAL_FILTER
,0 Image_Index
FROM SYS.ALL_OBJECTS A
WHERE A.OWNER NOT IN ('SYS', 'SYSTEM')
AND (A.OBJECT_TYPE = 'VIEW')
-- Synonyms
UNION ALL
SELECT
CAST('Synonyms' AS VARCHAR2(20)) OBJ_NAME,
CAST('Synonyms' AS VARCHAR2(20)) TEMPLATE_NAME,
count(*) SUB_OBJ_NAME,
CAST('TableObjects' AS VARCHAR2(20)) NODES_SQLCLASS_NAME,
CAST('atype = ''V'' and owner <> ''SYS''' AS VARCHAR2(100)) LOCAL_FILTER
,0 Image_Index
FROM SYS.ALL_SYNONYMS A
WHERE A.OWNER NOT IN ('SYS', 'SYSTEM')
--Snapshots
--Clusters
--TableSpaces
--Sequences
UNION ALL
SELECT
CAST('Sequences' AS VARCHAR2(20)) OBJ_NAME,
CAST('Sequences' AS VARCHAR2(20)) TEMPLATE_NAME,
count(*) SUB_OBJ_NAME,
CAST('SequencesObjects' AS VARCHAR2(20)) NODES_SQLCLASS_NAME,
CAST(' ' AS VARCHAR2(100)) LOCAL_FILTER
,0 Image_Index
FROM SYS.ALL_SEQUENCES A
WHERE A.SEQUENCE_OWNER NOT IN ('SYS', 'SYSTEM')
--Procedures
UNION ALL
SELECT
CAST('Procedures' AS VARCHAR2(20)) OBJ_NAME,
CAST('Procedures' AS VARCHAR2(20)) TEMPLATE_NAME,
count(*) SUB_OBJ_NAME,
CAST('TableObjects' AS VARCHAR2(20)) NODES_SQLCLASS_NAME,
CAST('atype = ''PR'' and owner <> ''informix''' AS VARCHAR2(100)) LOCAL_FILTER
,0 Image_Index
FROM
SYS.ALL_OBJECTS A
WHERE A.OWNER NOT IN ('SYS', 'SYSTEM')
AND (A.OBJECT_TYPE = 'PROCEDURE')
--Functions
UNION ALL
SELECT
CAST('Functions' AS VARCHAR2(20)) OBJ_NAME,
CAST('Functions' AS VARCHAR2(20)) TEMPLATE_NAME,
count(*) SUB_OBJ_NAME,
CAST('TableObjects' AS VARCHAR2(20)) NODES_SQLCLASS_NAME,
CAST('atype = ''PR'' and owner <> ''informix''' AS VARCHAR2(100)) LOCAL_FILTER
,0 Image_Index
FROM
SYS.ALL_OBJECTS A
WHERE A.OWNER NOT IN ('SYS', 'SYSTEM')
AND (A.OBJECT_TYPE = 'FUNCTION')
--Packages
UNION ALL
SELECT
CAST('Packages' AS VARCHAR2(20)) OBJ_NAME,
CAST('Packages' AS VARCHAR2(20)) TEMPLATE_NAME,
count(*) SUB_OBJ_NAME,
CAST('TableObjects' AS VARCHAR2(20)) NODES_SQLCLASS_NAME,
CAST('atype = ''PR'' and owner <> ''informix''' AS VARCHAR2(100)) LOCAL_FILTER
,0 Image_Index
FROM
SYS.ALL_OBJECTS A
WHERE A.OWNER NOT IN ('SYS', 'SYSTEM')
AND (A.OBJECT_TYPE = 'PACKAGE')
--Packages Bodies
UNION ALL
SELECT
CAST('Packages Bodies' AS VARCHAR2(20)) OBJ_NAME,
CAST('Packages Bodies' AS VARCHAR2(20)) TEMPLATE_NAME,
count(*) SUB_OBJ_NAME,
CAST('TableObjects' AS VARCHAR2(20)) NODES_SQLCLASS_NAME,
CAST('atype = ''PR'' and owner <> ''informix''' AS VARCHAR2(100)) LOCAL_FILTER
,0 Image_Index
FROM
SYS.ALL_OBJECTS A
WHERE A.OWNER NOT IN ('SYS', 'SYSTEM')
AND (A.OBJECT_TYPE = 'PACKAGE BODY')
--SysInfo
UNION ALL
SELECT
'SysInfo' OBJ_NAME,
'SysInfo' TEMPLATE_NAME,
count(*) SUB_OBJ_NAME,
CAST('SysInfo' AS VARCHAR2(20)) NODES_SQLCLASS_NAME,
CAST(' ' AS VARCHAR2(100)) LOCAL_FILTER
, 0 Image_Index
FROM dual
##################################################
#ORACLE.Tables
SELECT
A.OWNER
,A.TABLE_NAME
,DECODE(A.OWNER, USER, A.TABLE_NAME, A.OWNER || '.' || A.TABLE_NAME) VIS_TABLE_NAME
,DECODE(A.OWNER, USER, 1, 2) ORDER1
,A.OWNER || '.' || A.TABLE_NAME OWNER_TABLE_NAME
,'TableColumns' NODES_SQLCLASS_NAME
,'TableColumns' TEMPLATE_NAME
,T.COMMENTS DESCRIPTION
FROM SYS.ALL_TABLES A
,SYS.ALL_TAB_COMMENTS T
WHERE A.OWNER NOT IN ('SYS', 'SYSTEM')
AND A.OWNER = T.OWNER AND A.TABLE_NAME = T.TABLE_NAME
ORDER BY ORDER1, A.OWNER, A.TABLE_NAME
##################################################
#ORACLE.Tables.Columns
SELECT
A.COLUMN_ID
, A.DATA_TYPE
, A.DATA_LENGTH
, A.DATA_PRECISION
, A.DATA_SCALE
, A.NULLABLE
, A.DATA_DEFAULT
, A.OWNER
, A.TABLE_NAME
, A.COLUMN_NAME
, A.DATA_TYPE_MOD
, A.DATA_TYPE_OWNER
, A.DEFAULT_LENGTH
, A.NUM_DISTINCT
, A.LOW_VALUE
, A.HIGH_VALUE
, A.DENSITY
, A.NUM_NULLS
?? 快捷鍵說明
復制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -