几个sql语句的使用
从数据库中查找表或字段的注释:
select * from user_tab_comments where comments is not null;
select * from user_col_comments where comments is not null;
查询表名:
select table_name from tabs;
结果中包含指标有:
TABLE_NAME
TABLESPACE_NAME
CLUSTER_NAME
IOT_NAME
PCT_FREE
PCT_USED
INI_TRANS
MAX_TRANS
INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE FREELISTS FREELIST_GROUPS LOGGING BACKED_UP NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN AVG_SPACE_FREELIST_BLOCKS NUM_FREELIST_BLOCKS DEGREE INSTANCES CACHE TABLE_LOCK SAMPLE_SIZE LAST_ANALYZED PARTITIONED IOT_TYPE TEMPORARY SECONDARY NESTED BUFFER_POOL ROW_MOVEMENT GLOBAL_STATS USER_STATS DURATION SKIP_CORRUPT MONITORING CLUSTER_OWNER
查找表中meta数据的一个sql例子:(类似jdbc中的getMetaData()中的用法)
SELECT NULL AS table_cat,
t.owner AS table_schem,
t.table_name AS table_name,
t.column_name AS column_name,
DECODE (t.data_type, 'CHAR', 1, 'VARCHAR2', 12, 'NUMBER', 3,
'LONG', -1, 'DATE', 93, 'RAW', -3, 'LONG RAW', -4, 1111)
AS data_type,
t.data_type AS type_name,
DECODE (t.data_precision, null, t.data_length, t.data_precision)
AS column_size,
0 AS buffer_length,
t.data_scale AS decimal_digits,
10 AS num_prec_radix,
DECODE (t.nullable, 'N', 0, 1) AS nullable,
(select COMMENTS from user_col_comments where (table_name=t.TABLE_NAME) and ((COLUMN_NAME=t.COLUMN_NAME))) AS remarks,
t.data_default AS column_def,
0 AS sql_data_type,
0 AS sql_datetime_sub,
t.data_length AS char_octet_length,
t.column_id AS ordinal_position,
DECODE (t.nullable, 'N', 'NO', 'YES') AS is_nullable
FROM all_tab_columns t where t.TABLE_NAME='CA10'
从oracle中手工查询注释(不同用户)
select o.name, c.name, co.comment$
from sys.obj$ o, sys.col$ c, sys.com$ co
where o.type# in (2, 4)
and o.obj# = c.obj#
and c.obj# = co.obj#(+)
and c.intcol# = co.col#(+)
and bitand(c.property, 32) = 0 and o.name='AC10';
![](/Images/OutliningIndicators/None.gif)
select o.name, c.name, co.comment$
from sys.obj$ o, sys.col$ c, sys.com$ co
where o.type# in (2, 4)
and o.obj# = c.obj#
and c.obj# = co.obj#(+)
and c.intcol# = co.col#(+)
and bitand(c.property, 32) = 0 and o.name='CA10'
and o.owner# = (select u.user# from sys.user$ u where u.name ='POC');
![](/Images/OutliningIndicators/None.gif)
select co.comment$
from sys.obj$ o, sys.col$ c, sys.com$ co
where o.type# in (2, 4)
and o.obj# = c.obj#
and c.obj# = co.obj#(+)
and c.intcol# = co.col#(+)
and bitand(c.property, 32) = 0 and o.name='AC10'
and o.owner# = (select u.user# from sys.user$ u where u.name ='POC');
SELECT A.TABLE_NAME,
A.COLUMN_ID,
A.COLUMN_NAME,
B.COMMENTS,
DECODE(A.DATA_TYPE,'VARCHAR2',A.DATA_TYPE||'('||TO_CHAR(A.DATA_LENGTH)||')',
'NUMBER',A.DATA_TYPE
||DECODE(A.DATA_PRECISION,NULL,NULL,
'('||TO_CHAR(A.DATA_PRECISION)
||DECODE(A.DATA_SCALE,NULL,NULL,0,NULL,
','||TO_CHAR(A.DATA_SCALE))
||')' ),
'CHAR',A.DATA_TYPE||'('||TO_CHAR(A.DATA_LENGTH)||')',
'FLOAT',A.DATA_TYPE||'('||TO_CHAR(A.DATA_PRECISION)||')',
'DATE',A.DATA_TYPE,
A.DATA_TYPE ) DATA_TYPE,
A.NULLABLE,
DECODE(SUBSTR(C.CONSTRAINT_NAME,1,3),'PK_','Y','N') IS_PRIMARY_KEY,
C.POSITION
FROM COLS A,USER_COL_COMMENTS B,USER_CONS_COLUMNS C
WHERE A.COLUMN_NAME = B.COLUMN_NAME
AND A.TABLE_NAME = B.TABLE_NAME
AND A.TABLE_NAME = C.TABLE_NAME(+)
AND A.COLUMN_NAME = C.COLUMN_NAME(+)
AND C.OWNER(+) = USER
AND C.CONSTRAINT_NAME(+) LIKE 'PK_%'
数据库是dbf格式的,导数据到sql server 2000,代码如下(看的别人的,不过,sql server 2000的帮助中有):
SELECT * INTO bmk
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0','Data Source="e:\share";User ID=Admin;Password=;Extended properties=dBase 5.0')
bmk
这样就可以把e:\share中的bmk.dbf表导入到Sqlserver中,速度是最快的
select * from user_tab_comments where comments is not null;
select * from user_col_comments where comments is not null;
查询表名:
select table_name from tabs;
结果中包含指标有:
TABLE_NAME
TABLESPACE_NAME
CLUSTER_NAME
IOT_NAME
PCT_FREE
PCT_USED
INI_TRANS
MAX_TRANS
INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE FREELISTS FREELIST_GROUPS LOGGING BACKED_UP NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN AVG_SPACE_FREELIST_BLOCKS NUM_FREELIST_BLOCKS DEGREE INSTANCES CACHE TABLE_LOCK SAMPLE_SIZE LAST_ANALYZED PARTITIONED IOT_TYPE TEMPORARY SECONDARY NESTED BUFFER_POOL ROW_MOVEMENT GLOBAL_STATS USER_STATS DURATION SKIP_CORRUPT MONITORING CLUSTER_OWNER
查找表中meta数据的一个sql例子:(类似jdbc中的getMetaData()中的用法)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
从oracle中手工查询注释(不同用户)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
数据库是dbf格式的,导数据到sql server 2000,代码如下(看的别人的,不过,sql server 2000的帮助中有):
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/dot.gif)
这样就可以把e:\share中的bmk.dbf表导入到Sqlserver中,速度是最快的