V_AllTable 表信息 数据库 SQL2008

本文详细介绍了如何使用SQL查询获取表的元数据,包括字段名、数据类型、是否为主键、是否为唯一标识符、长度、是否允许为空、默认值、注释信息等,并按字段顺序排列。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

SELECT     TOP (100) PERCENT d.name AS TableName, a.colorder AS Orders, a.name AS FieldName,
            (CASE WHEN COLUMNPROPERTY(a.id, a.name, 'IsIdentity')  = 1 THEN '√' ELSE '' END) AS Identitys,
            (CASE WHEN    (SELECT     COUNT(*)  FROM    sysobjects WHERE  (name IN
            (SELECT     name    FROM          sysindexes   WHERE      (id = a.id) AND (indid IN
            (SELECT     indid  FROM          sysindexkeys  WHERE      (id = a.id) AND (colid IN
            (SELECT     colid  FROM          syscolumns  WHERE      (id = a.id) AND (name = a.name))))))) AND (xtype = 'PK')) > 0 THEN '√' ELSE '' END) AS MainKey,
            b.name AS Types, a.length AS Bytes, COLUMNPROPERTY(a.id, a.name, 'PRECISION') AS Lengths,
            ISNULL(COLUMNPROPERTY(a.id, a.name, 'Scale'),  0) AS DecimalLength,
            (CASE WHEN a.isnullable = 1 THEN '√' ELSE '' END) AS IS_NUll, ISNULL(e.text, '') AS Defaults, ISNULL(g.value, '') AS Field_Info,
            CASE d .xtype WHEN 'U' THEN 'Table' ELSE 'View' END AS Table_View, d.crdate AS CreateDate
            FROM         sys.syscolumns AS a LEFT OUTER JOIN       sys.systypes AS b ON a.xtype = b.xusertype INNER JOIN
            sys.sysobjects AS d ON a.id = d.id AND (d.xtype = 'U' OR  d.xtype = 'V') AND d.name <> 'dtproperties' LEFT OUTER JOIN
            sys.syscomments AS e ON a.cdefault = e.id LEFT OUTER JOIN sys.extended_properties AS g ON a.id = g.major_id AND a.colid = g.major_id
            ORDER BY a.id, orders


 

SET SERVEROUTPUT ON; SET LONG 1000000; SET PAGESIZE 0; SET FEEDBACK OFF; SET VERIFY OFF; DECLARE CURSOR c_tables IS SELECT table_name FROM all_tables WHERE owner = 'JGBS_HISOFT' AND table_name LIKE 'BEEHIVE_CALIBER' ORDER BY table_name; CURSOR c_columns (p_table_name VARCHAR2) IS SELECT column_name, data_type, data_length, data_precision, data_scale FROM all_tab_columns WHERE owner = 'JGBS_HISOFT' AND table_name = p_table_name ORDER BY column_id; CURSOR c_pk_constraint (p_table_name VARCHAR2) IS SELECT ucc.column_name FROM all_constraints uc JOIN all_cons_columns ucc ON uc.constraint_name = ucc.constraint_name WHERE uc.owner = 'JGBS_HISOFT' AND uc.table_name = p_table_name AND uc.constraint_type = 'P' ORDER BY ucc.position; CURSOR c_table_comments (p_table_name VARCHAR2) IS SELECT comments FROM all_tab_comments WHERE owner = 'JGBS_HISOFT' AND table_name = p_table_name; CURSOR c_column_comments (p_table_name VARCHAR2) IS SELECT column_name, comments FROM all_col_comments WHERE owner = 'JGBS_HISOFT' AND table_name = p_table_name ORDER BY column_name; v_table_name VARCHAR2(30); v_new_table_name VARCHAR2(30); v_column_defs CLOB; v_partition_column VARCHAR2(30) := 'EDW_ETL_DT'; v_sql CLOB; v_table_comment CLOB; v_comment_sql CLOB; v_pk_sql CLOB; BEGIN FOR table_rec IN c_tables LOOP v_table_name := table_rec.table_name; v_new_table_name := 'TCL_UCL135_' || v_table_name; v_column_defs := ''; v_comment_sql := ''; v_pk_sql := ''; -- 获取注释 FOR tbl_comm IN c_table_comments(v_table_name) LOOP v_table_comment := tbl_comm.comments; EXIT; END LOOP;
最新发布
03-27
<think>好的,我需要帮助用户通过PL/SQL提取Oracle数据库中指定所有者(JGBS_HISOFT)的名、列详情、主键约束以及和列的注释。首先,我需要回忆Oracle的数据字典视图相关知识,因为系统信息通常存储在这些视图中。 用户提到了结构、列定义、主键约束和注释,所以应该分别查询不同的数据字典视图。例如,信息可能在DBA_TABLES或ALL_TABLES中,列信息在DBA_TAB_COLUMNS,主键约束在DBA_CONSTRAINTS和DBA_CONS_COLUMNS,注释则在DBA_TAB_COMMENTS和DBA_COL_COMMENTS。不过需要注意权限问题,用户是否有访问DBA_视图的权限,如果没有的话可能需要使用ALL_或USER_视图。 接下来,名和所有者可以通过查询DBA_TABLES,条件是OWNER = 'JGBS_HISOFT'。列详情包括列名、数据类型、长度等,需要连接DBA_TAB_COLUMNS,同样过滤所有者。主键约束可能需要先从DBA_CONSTRAINTS找到约束类型为PRIMARY KEY的记录,再通过DBA_CONS_COLUMNS获取具体列。注释的话,和列分别有对应的注释视图,需要分别查询。 然后,编写PL/SQL脚本可能需要使用游标来遍历这些视图,并输出结果。例如,使用DBMS_OUTPUT来打印信息。不过用户可能需要的是生成脚本,而不是在运行时输出,所以可能需要动态生成DDL语句。不过根据引用[3],用户提到使用“create table 备份名 as select * from 名”的方式备份数据,但结构如主键、索引等不在,所以用户可能需要导出结构,包括约束和注释,而不仅仅是数据。 此外,引用[1]中的exp命令可能已经过时,但用户的问题是关于PL/SQL脚本,所以可能需要使用SQL查询结合数据字典来提取元数据。另外,引用[3]提到字段备注和主键、索引不在备份中,所以用户希望获取这些信息,因此需要明确包含这些元素的查询。 可能的步骤: 1. 获取所有名:从DBA_TABLES中选择TABLE_NAME,过滤OWNER。 2. 获取列详情:从DBA_TAB_COLUMNS中选择列名、数据类型、长度、是否可为空等。 3. 主键约束:连接DBA_CONSTRAINTS和DBA_CONS_COLUMNS,找到每个的主键列。 4. 注释:从DBA_TAB_COMMENTS获取。 5. 列注释:从DBA_COL_COMMENTS获取。 需要注意的是,如果用户没有DBA权限,可能需要使用ALL_TABLES等视图,并确保查询时指定正确的OWNER。另外,主键可能由多个列组成,需要处理这种情况,可能需要聚合多个列名。 然后,将这些信息组合成查询语句,可能需要多个SQL语句分别提取不同部分,或者使用JOIN来合并相关信息。例如,对于每个,列出列信息,然后关联主键信息和注释。 最后,将这些查询整合到PL/SQL脚本中,可能使用游标循环处理每个,并输出所需的信息。或者生成一个综合的查询结果,用户可以直接执行获取所有信息。</think>### 分步说明 通过PL/SQL提取Oracle数据库中指定所有者的结构信息,需查询系统数据字典视图。以下是具体步骤和示例脚本: --- #### **1. 获取名和注释** 查询`DBA_TABLES`和`DBA_TAB_COMMENTS`视图: ```sql SELECT t.TABLE_NAME, c.COMMENTS FROM DBA_TABLES t LEFT JOIN DBA_TAB_COMMENTS c ON t.OWNER = c.OWNER AND t.TABLE_NAME = c.TABLE_NAME WHERE t.OWNER = 'JGBS_HISOFT'; ``` --- #### **2. 获取列详情和列注释** 查询`DBA_TAB_COLUMNS`和`DBA_COL_COMMENTS`视图: ```sql SELECT COLUMN_NAME, DATA_TYPE, DATA_LENGTH, NULLABLE, COMMENTS FROM ( SELECT col.TABLE_NAME, col.COLUMN_NAME, col.DATA_TYPE, col.DATA_LENGTH, col.NULLABLE, com
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值