oracle之表信息查询

---

---

个人笔记，如有描述不当，欢迎留言指出~

起因

主管抽风，想查询数据库’dbo‘中各表字段结构、外键信息、索引信息。于是任务交给了我…

语句

-- 各表字段结构
SELECT
  TABLE_SCHEMA   数据库,
  table_name     表名,
  column_name    字段名,
  data_type      类型,
  is_nullable    是否为空,
  column_default 默认值
FROM information_schema.columns
WHERE TABLE_SCHEMA = 'dbo'

-- 各表外键信息
SELECT
  object_name(parent_object_id)                        表名,
  object_name(constraint_object_id)                    约束名称,
  col_name(parent_object_id, parent_column_id)         外键字段,
  object_name(referenced_object_id)                    关联表名,
  col_name(referenced_object_id, referenced_column_id) 关联表字段
FROM sys.foreign_key_columns
WHERE referenced_object_id IN (
  SELECT object_id(T.table_name)
  FROM (
         SELECT table_name
         FROM information_schema.columns
         WHERE TABLE_SCHEMA = 'dbo'
       ) T
)
ORDER BY object_name(parent_object_id)

-- 各表索引信息
SELECT
  object_name(object_id) 表名,
  name                   索引名,
  is_unique              是否唯一
FROM sys.indexes
WHERE Object_id IN (
  SELECT object_id(T.table_name)
  FROM (
         SELECT table_name
         FROM information_schema.columns
         WHERE TABLE_SCHEMA = 'dbo'
       ) T
)
ORDER BY 表名