0

I can list the schemas names with PostgreSQL for example using the query:

select schema_name from information_schema.schemata

What query is equivalent for Oracle and SQLServer? Thank you very much!

Juscelino Júnior
  • 9
  • 2
  • There is no `information_schema` in Oracle –  Jun 21 '21 at 15:24
  • @a_horse_with_no_name I have definitely used INFORMATION_SCHEMA on at least one Oracle installation. However, there were several others where I could not find/use it, so I was never sure what was going on (I don't know Oracle nearly as well as SQL Server). Maybe it's only available as an add-on pack? – RBarryYoung Jun 21 '21 at 15:29
  • No, `information_schema` is most definitely not available in Oracle. Maybe you are confusing it with MySQL? Or someone in your organization wrote the views manually. –  Jun 21 '21 at 15:32
  • No it was definitely on a large Oracle11g customer installation that I worked on for over a year. Maybe it was something like [this](https://renenyffenegger.ch/notes/development/databases/Oracle/INFORMATION_SCHEMA/index) that one of their corporate DBAs installed? I was working for one small department of a *huge* company that had pre-packaged corporate standard installations, so it might have been something that they mandated for their universal management tools. – RBarryYoung Jun 21 '21 at 15:34
  • Does this answer your question? [Oracle equivalent of information\_schema.tables](https://stackoverflow.com/questions/55037468/oracle-equivalent-of-information-schema-tables) – SMor Jun 21 '21 at 16:10

3 Answers3

0

As of Oracle:

SQL> connect sys as sysdba
Enter password:
Connected.
SQL> select * from all_users;

USERNAME                          USER_ID CREATED
------------------------------ ---------- --------
XS$NULL                        2147483638 29.05.14
MIKE                                   50 08.03.21
SCOTT                                  48 26.12.20
APEX_040000                            47 29.05.14
APEX_PUBLIC_USER                       45 29.05.14
FLOWS_FILES                            44 29.05.14
<snip>

Don't know about MS SQL Server.

Littlefoot
  • 131,892
  • 15
  • 35
  • 57
0

connect to database using SQLdeveloper or SQL developer then run the below query to fetch schema details

SELECT * FROM dba_users

VichuCoder
  • 21
  • 3
  • _"connect to database using SQLdeveloper or SQL developer"_ Hmm. What's the difference between 'SQLdeveloper' and 'SQL developer'? How to choose which one to use? – EdStevens Jun 21 '21 at 19:28
0

In theory this should work the same on both Oracle and SQL Server as it does on Postgres. I know that it works on SQL Server, however on Oracle I vaguely recall that there's some trick or setting to access (or enable?) the INFORMATION_SCHEMA tables.

Both Oracle and SQL Server also have their own custom system tables/views for stuff like this (sys.schemas for SQL Server and dba_users for Oracle, I think). SQL Server treats Schemas and Users as independent objects (Users can own Schemas) whereas Oracle treats them as different attributes/features of the same object (User == Schema), I'm not sure how Postgres does it.

Also, both SQL Server and Oracle will apply security "hiding" to objects that you do not have privs to see, even through INFORMATION_SCHEMA. So if you are not supposed to be able to "View" a certain schema, then it won't appear to you in the INFORMATION_SCHEMA.SCHEMATA table either. However, in many cases you are permitted to view metadata like this even when you are not permitted to access the objects themselves (depends on security settings).

RBarryYoung
  • 55,398
  • 14
  • 96
  • 137