ORACLE中查询当前用户下的所有表，某个表的表结构

ゝ一世哀愁。 2022-10-02 11:58 254阅读 0赞

SELECT
    	table_name 
    FROM
    	user_tables 
    WHERE
    	TABLESPACE_NAME = 'PROD_ENTITY' 
    	AND USER = 'PROD_ENTITY' 
    	AND TABLE_NAME NOT LIKE '%UDIC_%'

查看某个表的表结构，包括字段名称，字段类型，字段长度，是否为空，是否主键

SELECT
    	c.*,
    	d.comments column_comment 
    FROM
    	(
    	SELECT
    		a.column_name,
    		a.data_type || '(' || data_length || ')' column_type,
    		a.nullable is_nullable,
    	CASE
    			
    			WHEN b.column_name IS NOT NULL THEN
    			'PK' ELSE '' 
    		END column_key 
    FROM
    	user_tab_columns a
    	LEFT JOIN ( SELECT cu.* FROM user_cons_columns cu, user_constraints au WHERE cu.constraint_name = au.constraint_name AND au.constraint_type = 'P' ) b ON b.table_name = a.Table_Name 
    	AND a.column_name = b.column_name 
    WHERE
    	a.Table_Name = 'META_FIELD' 
    	) c
    	LEFT JOIN user_col_comments d ON c.column_name = d.column_name 
    WHERE
    	d.table_name = 'META_FIELD'

加载某个库的所有表已经所有表的所有字段，类型，长度，注释等

SELECT
    	c.TABLE_NAME  ,
    	t.COMMENTS TABLE_COMMENTS,
    	cc.COLUMN_NAME ,
    	cc.COMMENTS COLUMN_COMMENTS ,
    	c.DATA_TYPE,
    	c.DATA_LENGTH ,
    	c.DATA_SCALE,
    	c.NULLABLE 
    FROM
    	dba_tab_columns c
    	LEFT JOIN dba_col_comments cc ON c.table_name = cc.table_name 
    	AND c.column_name = cc.column_name
    	LEFT JOIN dba_tab_comments t ON c.table_name = t.table_name 
    WHERE
    	c.OWNER = 'PROD_ENTITY_ZGND' 
    	-- BIN 开头的表是已经删除了的表，oracle中存在类似于回收站的东西，删除了的表就放在其中 --
    	AND c.table_name NOT LIKE 'BIN$%==$0' 
    ORDER BY
    	c.table_name;