![query to list all tables in oracle database query to list all tables in oracle database](https://sites.google.com/site/craigsoraclestuff/oracle-forms---how-to-s/forms-how-to-base-a-data-block-on-a-table-join/picture1.png)
- #Query to list all tables in oracle database update
- #Query to list all tables in oracle database plus
(Ex:- Here Rajeev is my schema name you can use your schema which you want to check size)
#Query to list all tables in oracle database plus
for indexes it will be the size of the parent * table plus sizes of all the indexes on that table. Total database size occupied by the parent table.The number of extents allocated for the object.Database space occupied by the object in megabytes.indexes are associated with their parent tables. Name of the table this object is associated with.Object name and type (TABLE, INDEX, etc.).It shows the size of all the database objects large than 10 Mb in a particular database schema. See PRAGMA table_list in SQLite for an overview and examples.As a DBA sometimes you need to know how what’s taking space in an Oracle database, or how large is the table you’re working on, here’s a script which answers these questions. Since I first wrote this article, SQLite has introduced the table_list pragma statement, which lists out tables and views: PRAGMA table_list ORDER BY name Bonus 3rd Option: The table_list Pragma Statement To return both permanent tables and temporary tables, you can use a query like this: SELECT name FROM If you need to return just the temporary tables, you can query sqlite_temp_schema or its synonym sqlite_temp_master. The sqlite_schema table only contains permanent tables. table command returns both permanent tables and temporary tables. Now query sqlite_schema for tables only: SELECT name FROM sqlite_schema Query the sqlite_schema table for tables and views: SELECT name FROM sqlite_schema This database contains one table (called Products) and one view (called vProducts).Ĭonnect to SQLite/the database: sqlite3 Store.db Exclude Viewsįor the sake of completeness, here’s a quick example that uses a database with a view. The sqlite_schema table can also be accessed by using sqlite_master. In my case there aren’t any views, but if you want to exclude views in the results, use this: SELECT name FROM sqlite_schema That query returns both tables and views (just as the. Running the above query returns the following result: Album tables command returns results for all attached databases). The difference is that this method only returns results for the primary database (the. tables command, it’s similar to doing this: SELECT name FROM sqlite_schema The sqlite_schema TableĮvery SQLite database has an sqlite_schema table that defines the schema for the database. You can use this table to return a list of tables in your database. Although this table also contains views, you can use SQL to exclude them from your results if need be. This will only work if your views use a naming convention that distinguishes them from tables and other objects.Īnother way to exclude views from your results is to query the sqlite_schema table directly. If you want to exclude views from your results, you could use pattern matching to exclude views. tables command returns both tables and views. In this case only tables that begin with the letter “a” are returned. For example, you can name a specific table, or you can use pattern matching to return only tables that match a given pattern. Such an argument can be used to limit the tables returned by the command. In my case, there’s only one attached database (the Chinook sample database), and all of this database’s tables are returned.Īs mentioned, you can also provide an argument to this command. Result: Album Employee InvoiceLine PlaylistTrack If you use it without providing an argument, it returns all tables (and views) for all attached databases. This command can be used with or without an argument. The easiest way to return a list of tables when using the SQLite command line shell is to use the.
#Query to list all tables in oracle database update
Update Dec 2021: Since writing this article, SQLite has introduced another option, which I’ve listed as a bonus third option at the end of this article. The second method gives you the option of returning both tables and views, or just tables, but only for the primary database. The first method returns all tables and views for all attached databases. Here are two ways to return a list of tables in all attached databases in SQLite.