-----------------------------------------oracle-------------------------------
1、查找表的所有索引（包括索引名，类型，构成列）
SELECT t.*,i.index_type from user_ind_columns t,user_indexes i where t.index_name = i.index_name and t.table_name = i.table_name and t.table_name = 要查询的表
2、查找表的主键（包括名称，构成列）
SELECT cu.* from user_cons_columns cu, user_constraints au where cu.constraint_name = au.constraint_name and au.constraint_type = 'P' and au.table_name = 要查询的表
3、查找表的唯一性约束（包括名称，构成列）
SELECT column_name from user_cons_columns cu, user_constraints au where cu.constraint_name = au.constraint_name and au.constraint_type = ‘U‘ and au.table_name = 要查询的表
4、查找表的外键（包括名称，引用表的表名和对应的键名，下面是分成多步查询）
SELECT * from user_constraints c where c.constraint_type = ‘R‘ and c.table_name = 要查询的表
查询外键约束的列名
SELECT * from user_cons_columns cl where cl.constraint_name = 外键名称
查询引用表的键的列名
SELECT * from user_cons_columns cl where cl.constraint_name = 外键引用表的键名
5、查询表的所有列及其属性
SELECT t.*,c.COMMENTS from user_tab_columns t,user_col_comments c where t.table_name = c.table_name and t.column_name = c.column_name and t.table_name = 要查询的表
----------------------------主键--------------------------
注：表名大写
SELECT CU.*,AU.*
FROM USER_CONS_COLUMNS CU, USER_CONSTRAINTS AU
WHERE CU.CONSTRAINT_NAME = AU.CONSTRAINT_NAME
--AND AU.CONSTRAINT_TYPE = 'P'
AND AU.TABLE_NAME = 'SYSTEM_TB_DICTIONARY'
----oracle 主键
SELECT * FROM USER_CONS_COLUMNS T11
LEFT JOIN USER_CONSTRAINTS T12 ON T11.CONSTRAINT_NAME = T12.CONSTRAINT_NAME
WHERE T12.TABLE_NAME = TABLENAME
AND T12.CONSTRAINT_TYPE = 'P'
----表空间信息
select * from user_segments where segment_name ='TB_ADM_DEPT'
----oracle所有表
SELECT * FROM ALL_TAB_COMMENTS TAB
WHERE TAB.OWNER = 'ERDS' AND TAB.TABLE_TYPE = 'TABLE'
SELECT * FROM USER_TAB_COMMENTS
---oracle删除表和视图
select 'drop table ' || table_name ||';'||chr(13)||chr(10) from user_tables; --delete tables
select 'drop view ' || view_name||';'||chr(13)||chr(10) from user_views; --delete views
select 'drop sequence ' || sequence_name||';'||chr(13)||chr(10) from user_sequences;--delete seqs
select 'drop function ' || object_name||';'||chr(13)||chr(10) from user_objects where object_type='FUNCTION';--delete functions
select 'drop procedure ' || object_name||';'||chr(13)||chr(10) from user_objects where object_type='PROCEDURE';--DELETE PROCEDURE
select 'drop package ' || object_name||';'||chr(13)||chr(10) from user_objects where object_type='PACKAGE';--delete pags
select 'alter table '||table_name||' drop constraint '||constraint_name||';' from user_constraints where constraint_type='R'; --drop fk