oracle--取出表字段，注释

最新推荐文章于 2024-07-31 17:06:19 发布

原创最新推荐文章于 2024-07-31 17:06:19 发布 · 187 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#Oracle #VB #SQL #SQL Server #VB.NET

数据库专栏收录该内容

34 篇文章

订阅专栏

本文提供在Oracle和SQL Server数据库中查询表、视图及其注释的SQL语句。包括获取表名、表类型及注释信息，以及如何查询特定表的列名和列注释等实用技巧。

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

遍历Oracle数据库获取表名和注释

select a.TABLE_NAME,TABLESPACE_NAME ,b.TABLE_TYPE,B.COMMENTS
from user_tables   a,user_tab_comments   b
WHERE A.TABLE_NAME=B.TABLE_NAME
order by TABLE_NAME

遍历Oracle数据库获取视图名和注释

select a.view_NAME ,b.TABLE_TYPE,B.COMMENTS
from user_views   a,user_tab_comments   b
WHERE A.view_NAME=B.TABLE_NAME
order by TABLE_NAME

oracle里取出注释的语句


strsql = "select * from user_col_comments where comments is not null"


select * from user_tab_columns  
where table_name = Upper('t_wp_s401');


select   column_name    
from   dba_tab_columns    
where   owner=upper('xxx')    
and   table_name='t_wp_s401';   

select column_name,comments
from user_col_comments  where table_name = Upper('t_wp_s401') ;

sql server里取出注释的语句


SELECT Sysobjects.name AS TABLE_NAME,sys.extended_properties.[value] AS COLUMN_DESCRIPTION  FROM syscolumns
INNER JOIN systypes
    ON syscolumns.xtype = systypes.xtype
    LEFT JOIN sysobjects ON syscolumns.id = sysobjects.id
   LEFT OUTER JOIN sys.extended_properties ON
   ( sys.extended_properties.minor_id = syscolumns.colid
     AND sys.extended_properties.major_id = syscolumns.id)
   LEFT OUTER JOIN syscomments ON syscolumns.cdefault = syscomments.id
   WHERE syscolumns.id IN 
    (SELECT id FROM SYSOBJECTS WHERE xtype = 'U') AND (systypes.name <> 'sysname') and (Sysobjects.name='table10')
    ORDER BY syscolumns.colid

vb.net 里取出注释的语句


test = "SELECT Sysobjects.name AS BLE_NAME,sys.extended_properties.

[value] AS COLUMN_DESCRIPTION FROM syscolumns INNER JOIN systypes ON syscolumns.xtype = systypes.xtype LEFT JOIN sysobjects ON syscolumns.id = sysobjects.id LEFT OUTER JOIN sys.extended_properties ON ( sys.extended_properties.minor_id = syscolumns.colid AND sys.extended_properties.major_id = syscolumns.id) LEFT OUTER JOIN syscomments ON syscolumns.cdefault = syscomments.id WHERE syscolumns.id IN  (SELECT id FROM SYSOBJECTS WHERE xtype = 'U') AND (systypes.name <> 'sysname') and (Sysobjects.name='table10') ORDER BY syscolumns.colid"