Oracle查找数据库中没有主键的表

最新推荐文章于 2024-01-25 09:30:52 发布

骑着长颈鹿敲代码

最新推荐文章于 2024-01-25 09:30:52 发布

阅读量2.3k

点赞数 2

分类专栏：数据库文章标签： oracle

本文链接：https://blog.csdn.net/weixin_46421519/article/details/123275149

版权

数据库专栏收录该内容

6 篇文章 0 订阅

订阅专栏

这篇博客分享了如何使用SQL查询在Oracle数据库中找出没有主键的表，区分是否以'SYS'开头，并进一步查询所有空表和非空表。通过LEFT JOIN操作结合所有非'SYS'开头的空表，提供了一种全面了解数据库表状态的方法。

摘要由CSDN通过智能技术生成

Oracle查找数据库中没有主键的表

--没有主键的表
select table_name
  from user_tables a
 where not exists (select *
          from user_constraints b
         where b.constraint_type = 'P'
           and a.table_name = b.table_name)
           
           
--SYS开头的       

select table_name
  from user_tables a
 where not exists (select *
          from user_constraints b
         where b.constraint_type = 'P'
           and a.table_name = b.table_name)
and table_name  LIKE 'SYS%' 


--不是SYS开头的       

select table_name
  from user_tables a
 where not exists (select *
          from user_constraints b
         where b.constraint_type = 'P'
           and a.table_name = b.table_name)
and table_name NOT LIKE 'SYS%' 

--所有空表
select table_name from all_all_tables where owner='MDM' AND num_rows ='0'
--所有非空表且不是SYS开头
SELECT x.* FROM (select table_name
  from user_tables a
 where not exists (select *
          from user_constraints b
         where b.constraint_type = 'P'
           and a.table_name = b.table_name)
and table_name NOT LIKE 'SYS%')x LEFT JOIN (select table_name from all_all_tables where owner='MDM' AND num_rows ='0')y ON x.table_name = y.table_name
WHERE y.table_name IS NULL
--所有不是SYS开头的空表
SELECT x.* FROM (select table_name
  from user_tables a
 where not exists (select *
          from user_constraints b
         where b.constraint_type = 'P'
           and a.table_name = b.table_name)
and table_name NOT LIKE 'SYS%')x LEFT JOIN (select table_name from all_all_tables where owner='MDM' AND num_rows ='0')y ON x.table_name = y.table_name
WHERE y.table_name IS not null