SELECT * FROM user_tab_columns WHERE column_name = 'ABC' --ABC 是你要找的字段名
各个楼上都对,如果楼主还没搞定那就啥也不说了。提醒两件事吧,要不也不好意思蹭分:1、记不清楚列名字还可以用like嘛。2、为了防止你猪头般的使用了小写的字段名:SELECT * FROM user_tab_columns WHERE Upper(column_name) = Upper('ABC')
select table_name from all_col_comments where upper(column_name) = upper(字段名); 会列出所有包含该字段的表。
弱弱問一句 all_col_comments 是在哪個表空間的??
all_col_comments是sys用户的一个视图,并且sys通过建立同名(all_col_comments)的public同义词(synonyms)暴露给其他用户使用的。让你用起来就感觉在当前用户下有all_col_comments表一样。视图的脚本如下,其中引用的对象都在system表空间下: CREATE OR REPLACE VIEW ALL_COL_COMMENTS (OWNER, TABLE_NAME, COLUMN_NAME, COMMENTS) AS select u.name, o.name, c.name, co.comment$ from sys.obj$ o, sys.col$ c, sys.user$ u, sys.com$ co where o.owner# = u.user# and o.type# in (2, 4, 5) and o.obj# = c.obj# and c.obj# = co.obj#(+) and c.intcol# = co.col#(+) and bitand(c.property, 32) = 0 /* not hidden column */ and (o.owner# = userenv('SCHEMAID') or o.obj# in (select obj# from sys.objauth$ where grantee# in ( select kzsrorol from x$kzsro ) ) or exists (select null from v$enabledprivs where priv_number in (-45 /* LOCK ANY TABLE */, -47 /* SELECT ANY TABLE */, -48 /* INSERT ANY TABLE */, -49 /* UPDATE ANY TABLE */, -50 /* DELETE ANY TABLE */)) )
FROM user_tab_columns t1
WHERE t1.column_name = 'AAA' --用你想查询的字段名替换AAA即刻
查看数据库中所有的列SELECT *
FROM dba_tab_columns t1
WHERE t1.column_name = 'OBJECT_ID'
FROM dba_tab_columns where column_name='colname'
column_name='ABC',字段名ABC一定要用大写。
FROM user_tab_columns
WHERE column_name = 'ABC' --ABC 是你要找的字段名
FROM user_tab_columns
WHERE Upper(column_name) = Upper('ABC')
会列出所有包含该字段的表。
CREATE OR REPLACE VIEW ALL_COL_COMMENTS
(OWNER, TABLE_NAME, COLUMN_NAME, COMMENTS)
AS
select u.name, o.name, c.name, co.comment$
from sys.obj$ o, sys.col$ c, sys.user$ u, sys.com$ co
where o.owner# = u.user#
and o.type# in (2, 4, 5)
and o.obj# = c.obj#
and c.obj# = co.obj#(+)
and c.intcol# = co.col#(+)
and bitand(c.property, 32) = 0 /* not hidden column */
and (o.owner# = userenv('SCHEMAID')
or o.obj# in
(select obj#
from sys.objauth$
where grantee# in ( select kzsrorol
from x$kzsro
)
)
or
exists (select null from v$enabledprivs
where priv_number in (-45 /* LOCK ANY TABLE */,
-47 /* SELECT ANY TABLE */,
-48 /* INSERT ANY TABLE */,
-49 /* UPDATE ANY TABLE */,
-50 /* DELETE ANY TABLE */))
)