You can get a list of the schemas using an SSMS or T-SQL query. To do this in SSMS, you would connect to the SQL instance, expand the SQL database and view the schemas under the security folder. Alternatively, you could use the sys. schemas to get a list of database schemas and their respective owners.
To see all tables in another schema, you need to have one or more of the following system privileges:
SELECT ANY DICTIONARY
(SELECT | INSERT | UPDATE | DELETE) ANY TABLE
or the big-hammer, the DBA role.
With any of those, you can select:
SELECT DISTINCT OWNER, OBJECT_NAME
FROM DBA_OBJECTS
WHERE OBJECT_TYPE = 'TABLE'
AND OWNER = '[some other schema]'
Without those system privileges, you can only see tables you have been granted some level of access to, whether directly or through a role.
SELECT DISTINCT OWNER, OBJECT_NAME
FROM ALL_OBJECTS
WHERE OBJECT_TYPE = 'TABLE'
AND OWNER = '[some other schema]'
Lastly, you can always query the data dictionary for your own tables, as your rights to your tables cannot be revoked (as of 10g):
SELECT DISTINCT OBJECT_NAME
FROM USER_OBJECTS
WHERE OBJECT_TYPE = 'TABLE'
SELECT table_name from all_tables where owner = 'YOURSCHEMA';
You can query USER_TABLES
select TABLE_NAME from user_tables
If you logged in as Normal User without DBA permission you may uses the following command to see your own schema's all tables and views.
select * from tab;
select * from cat;
it will show all tables in your schema cat synonym of user_catalog
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With