How to check if a sequence exists in my schema?

Question

Is there a way to retrieve all the sequences defined in an existing oracle-sql db schema?

Ideally I would like to use something like this:

SELECT * FROM all_sequences WHERE owner = 'me';

which apparently doesn't work.

Bellbottoms

What are you looking for exactly and what does your table look like? – Antigen

Answer 1

Try this:

SELECT object_name
  FROM all_objects
 WHERE object_type = 'SEQUENCE' AND owner = '<schema name>'

Granville

Answer 2

Yes:

select * from user_sequences;

Your SQL was almost correct too:

select * from all_sequences where sequence_owner = user;

Wendy

Thanks Tony, this works as well, but I chose the other answer as it is more generic. – Bellbottoms

Answer 3

Below query can be triggered in Oracle Developer to check whether sequence present in DB or not :

SELECT count(*) count FROM user_sequences WHERE sequence_name = 'SEQ_NAME';

If 'SEQ_NAME' present in your DB then count will return 1 else 0 .

Dichromate