oracle查出表结构，以及导出Excel

1. 在navica执行以下sql

select 
aa.owner			table_schema	     	-- 数据库名
,aa.table_name 								--表名
,dd.table_comments	table_comment			--表注释
,aa.filed_name 		column_name	 	        -- 列名
,dd.filed_comments	column_comment			--列名注释
,cc.is_index								--是否是索引
,cc.index_name			  				    --字段名
,ee.column_type								--字段类型
,ee.is_null									--是否为空
,bb.primary_key								--是否是主键
,aa.col#	        ordinal_position		--字段顺序
--,bb.constraint_type      				    --主键标志   'p'

from 
	(
		select 
		obj.owner,   			  -- 数据库名				
		ll.name filed_name,       -- 字段名
		ll.col#,              			--字段顺序
		obj.object_name table_name
		FROM all_objects  obj
		left join sys.col$ ll on obj.object_id =ll.obj#
		WHERE obj.owner='yourDbName'
		and ll.name is not null

	)aa
---------------------------------主键显示处理-------------------------------
left join 
	(
		select 
		a.owner,						 --库名
		a.table_name,					 --表名
		a.column_name filed_name,        --列名
		b. constraint_type,              -- 主键形式 p -主键
		'是' as primary_key
		from all_cons_columns a, all_constraints b
		where a.constraint_name = b.constraint_name
		and  a.owner = 'yourDbName'
		and   b.constraint_type= 'P'

	)bb
on aa.filed_name = bb.filed_name
and aa.table_name = bb.table_name
and aa.owner = bb.owner

-----------------------------------索引处理---------------------------------
left join 
(
	select 
	cl.COLUMN_Name  filed_name   --列名
	,ai.INDEX_Name  index_name    --索引名
	,cl.table_name				  --表名
	,cl.table_owner owner         --库名
	,'是' as is_index
	from  all_ind_columns cl
	left join all_indexes ai  
	on  cl.index_name = ai.index_name
	where cl.table_owner = 'yourDbName'
	and UNIQUENESS != 'UNIQUE'    -- 排除主键索引
)cc
on aa.filed_name= cc.filed_name
and aa.table_name = cc.table_name
and aa.owner = cc.owner

----------------------------------------注释处理------------------------
left join (
		select 
		al_tables.owner                           --库名
		,al_tables.table_name					  --表名
		,al_tables.COMMENTs table_comments		  --表注释
		,al_filed.COLUMN_name filed_name		  --字段名	
		,al_filed.comments filed_comments		  --字段注释
		FROM 
		ALL_COL_COMMENTS al_filed 
		left join ALL_TAB_COMMENTS al_tables 
		on   al_tables.owner = al_filed.owner
		and   al_tables.table_name = al_filed.table_name
		where al_filed.owner = 'yourDbName'
		  )dd
on aa.filed_name= dd.filed_name
and aa.table_name= dd.table_name
and aa.owner = dd.owner
----------------------------------类型处理---------------------------------
left join(
			select 
			 owner,
			 table_name,
			 column_name filed_name,
			 nullable, 
			 CASE WHEN nullable='Y' THEN  '是' 
						ELSE '否' END  is_null,
			 data_type, 
			CASE WHEN  data_type='DATE' THEN  a.data_type 
				 WHEN data_type='TIMESTAMP(6)' THEN  a.data_type
				 WHEN data_type='NUMBER' and DATA_precision is  null THEN data_type	
				 WHEN data_type='NUMBER'THEN data_type || '(' || DATA_precision || ',' || data_scale ||')'
			     ELSE a.data_type || '(' || a.data_length || ')' END  column_type
			from all_tab_columns a
			WHERE  owner = 'yourDbName'   --库名

		 )ee
on aa.filed_name= ee.filed_name
and aa.table_name= ee.table_name		
and aa.owner = ee.owner
ORDER BY table_schema ,table_name,ordinal_position asc  

2. 将从navicat查询出来的数据导出excel,具体步骤见下图

   1.点击导出按钮
   2.导出当前的结果
   3.选择Excel数据表
   

   4.点击下一步
   
   5.选择包含列的标题，点击下一步

6.点出开始按钮