{"id":1185,"date":"2018-01-14T23:33:39","date_gmt":"2018-01-15T06:33:39","guid":{"rendered":"https:\/\/sqltutorial.org\/?page_id=1185"},"modified":"2020-04-04T06:56:23","modified_gmt":"2020-04-04T13:56:23","slug":"sql-list-all-tables","status":"publish","type":"page","link":"https:\/\/www.sqltutorial.org\/sql-list-all-tables\/","title":{"rendered":"SQL List All Tables"},"content":{"rendered":"\n<p><strong>Summary<\/strong>: in this tutorial, you will learn how to use commands to list all tables of a database in various database management systems.<\/p>\n\n\n\n<p>Each database system has its own command to show all tables in a specified database. Here you can find the respective SQL command to list all tables in MySQL, PostgreSQL, Oracle, SQL Server, DB2, and SQLite.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id='sql-command-to-list-all-tables-in-mysql'>SQL command to list all tables in MySQL <a href=\"#sql-command-to-list-all-tables-in-mysql\" class=\"anchor\" id=\"sql-command-to-list-all-tables-in-mysql\" title=\"Anchor for SQL command to list all tables in MySQL\">#<\/a><\/h2>\n\n\n\n<p>To list all tables in MySQL, first, you connect to the MySQL database server using the following command:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-1\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\">mysql -u username -p\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-1\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>MySQL then prompts for the password; just enter the correct one for the user and press enter.<\/p>\n\n\n\n<p>After that, select a database to work with:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-2\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">use<\/span> database_name;\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-2\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>And finally, issue the <code>SHOW TABLES<\/code> command to display all tables in the current database:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-3\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">show<\/span> <span class=\"hljs-keyword\">tables<\/span>;\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-3\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<h2 class=\"wp-block-heading\" id='sql-command-to-list-all-tables-in-oracle'>SQL command to list all tables in Oracle <a href=\"#sql-command-to-list-all-tables-in-oracle\" class=\"anchor\" id=\"sql-command-to-list-all-tables-in-oracle\" title=\"Anchor for SQL command to list all tables in Oracle\">#<\/a><\/h2>\n\n\n\n<p>In Oracle, you can use the SQL*Plus or SQL Developer <a href=\"http:\/\/www.oracletutorial.com\/getting-started\/connect-to-oracle-database\/\">connect to the Oracle Database server<\/a> and show all tables in a database. Then issue one of the following SQL statement:<\/p>\n\n\n\n<p>1) Show all tables owned by the current user:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-4\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">SELECT<\/span> \n    table_name\n<span class=\"hljs-keyword\">FROM<\/span>\n    user_tables;\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-4\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>2) Show all tables in the current database:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-5\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">SELECT<\/span> \n    table_name\n<span class=\"hljs-keyword\">FROM<\/span>\n    dba_tables;\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-5\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>3) Show all tables that are accessible by the current user:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-6\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">SELECT<\/span> \n    table_name\n<span class=\"hljs-keyword\">FROM<\/span>\n    all_tables;\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-6\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<h2 class=\"wp-block-heading\" id='sql-command-to-list-all-tables-in-postgresql'>SQL command to list all tables in PostgreSQL <a href=\"#sql-command-to-list-all-tables-in-postgresql\" class=\"anchor\" id=\"sql-command-to-list-all-tables-in-postgresql\" title=\"Anchor for SQL command to list all tables in PostgreSQL\">#<\/a><\/h2>\n\n\n\n<p>For <a href=\"http:\/\/www.postgresqltutorial.com\/psql-commands\/\">PostgreSQL<\/a>, you can use the psql command-line program to <a href=\"http:\/\/www.postgresqltutorial.com\/connect-to-postgresql-database\/\">connect to the PostgreSQL database server<\/a> and display all tables in a database.<\/p>\n\n\n\n<p>First, connect to the PostgreSQL Database server:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-7\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\">psql -d database_name -U  user -W\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-7\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>PostgreSQL will prompt for the password; just enter the correct one and press enter.<\/p>\n\n\n\n<p>Then, issue the following command to show all tables in the current database:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-8\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\">\\dt\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-8\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>If you want to display also the size and description of the tables, you the following command:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-9\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\">\\dt+ \n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-9\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<h2 class=\"wp-block-heading\" id='sql-command-to-list-all-tables-in-sql-server'>SQL command to list all tables in SQL Server <a href=\"#sql-command-to-list-all-tables-in-sql-server\" class=\"anchor\" id=\"sql-command-to-list-all-tables-in-sql-server\" title=\"Anchor for SQL command to list all tables in SQL Server\">#<\/a><\/h2>\n\n\n\n<p>In SQL Server, you can use the following query to find all tables in the currently connected database:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-10\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">SELECT<\/span> \n    *\n<span class=\"hljs-keyword\">FROM<\/span>\n    information_schema.tables;\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-10\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<h2 class=\"wp-block-heading\" id='sql-command-to-list-all-tables-in-db2'>SQL command to list all tables in DB2 <a href=\"#sql-command-to-list-all-tables-in-db2\" class=\"anchor\" id=\"sql-command-to-list-all-tables-in-db2\" title=\"Anchor for SQL command to list all tables in DB2\">#<\/a><\/h2>\n\n\n\n<p>First, connect to a specific database on the DB2 database server:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-11\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\">db2 connect to database_name\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-11\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Second, to list all table in the current database schema, you use the following command:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-12\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\">db2 list tables for schema schema_name\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-12\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>To list all tables, you use the command below:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-13\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\">db2 list tables for all\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-13\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<h2 class=\"wp-block-heading\" id='sql-command-to-list-all-tables-in-sqlite'>SQL command to list all tables in SQLite <a href=\"#sql-command-to-list-all-tables-in-sqlite\" class=\"anchor\" id=\"sql-command-to-list-all-tables-in-sqlite\" title=\"Anchor for SQL command to list all tables in SQLite\">#<\/a><\/h2>\n\n\n\n<p>To show all tables in the current SQLite database, you use the following command:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-14\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\">.tables \n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-14\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>If you want to query the tables based on a specific pattern e.g., all tables whose names start with <code>test<\/code>, you use the following command:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-15\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\">.tables 'test%';\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-15\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>In this tutorial, you have learned commands to show all tables in a database in various database systems including MySQL, PostgreSQL, Oracle, SQL Server, DB2, and SQLite.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>This tutorial shows you how to use commands to list all tables of a specified database in MySQL, PostgreSQL, Oracle, SQL Server, DB2, and SQLite.<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":0,"menu_order":68,"comment_status":"closed","ping_status":"closed","template":"","meta":{"footnotes":""},"class_list":["post-1185","page","type-page","status-publish","hentry"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.2 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>SQL List All tables<\/title>\n<meta name=\"description\" content=\"This tutorial shows you how to use commands to list all tables of a specified database in MySQL, PostgreSQL, Oracle, SQL Server, DB2, and SQLite.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.sqltutorial.org\/sql-list-all-tables\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SQL List All tables\" \/>\n<meta property=\"og:description\" content=\"This tutorial shows you how to use commands to list all tables of a specified database in MySQL, PostgreSQL, Oracle, SQL Server, DB2, and SQLite.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqltutorial.org\/sql-list-all-tables\/\" \/>\n<meta property=\"og:site_name\" content=\"SQL Tutorial\" \/>\n<meta property=\"article:modified_time\" content=\"2020-04-04T13:56:23+00:00\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data1\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqltutorial.org\/sql-list-all-tables\/\",\"url\":\"https:\/\/www.sqltutorial.org\/sql-list-all-tables\/\",\"name\":\"SQL List All tables\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqltutorial.org\/#website\"},\"datePublished\":\"2018-01-15T06:33:39+00:00\",\"dateModified\":\"2020-04-04T13:56:23+00:00\",\"description\":\"This tutorial shows you how to use commands to list all tables of a specified database in MySQL, PostgreSQL, Oracle, SQL Server, DB2, and SQLite.\",\"breadcrumb\":{\"@id\":\"https:\/\/www.sqltutorial.org\/sql-list-all-tables\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqltutorial.org\/sql-list-all-tables\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqltutorial.org\/sql-list-all-tables\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqltutorial.org\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SQL List All Tables\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqltutorial.org\/#website\",\"url\":\"https:\/\/www.sqltutorial.org\/\",\"name\":\"SQL Tutorial\",\"description\":\"An Interactive SQL Tutorial\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqltutorial.org\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"SQL List All tables","description":"This tutorial shows you how to use commands to list all tables of a specified database in MySQL, PostgreSQL, Oracle, SQL Server, DB2, and SQLite.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.sqltutorial.org\/sql-list-all-tables\/","og_locale":"en_US","og_type":"article","og_title":"SQL List All tables","og_description":"This tutorial shows you how to use commands to list all tables of a specified database in MySQL, PostgreSQL, Oracle, SQL Server, DB2, and SQLite.","og_url":"https:\/\/www.sqltutorial.org\/sql-list-all-tables\/","og_site_name":"SQL Tutorial","article_modified_time":"2020-04-04T13:56:23+00:00","twitter_card":"summary_large_image","twitter_misc":{"Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqltutorial.org\/sql-list-all-tables\/","url":"https:\/\/www.sqltutorial.org\/sql-list-all-tables\/","name":"SQL List All tables","isPartOf":{"@id":"https:\/\/www.sqltutorial.org\/#website"},"datePublished":"2018-01-15T06:33:39+00:00","dateModified":"2020-04-04T13:56:23+00:00","description":"This tutorial shows you how to use commands to list all tables of a specified database in MySQL, PostgreSQL, Oracle, SQL Server, DB2, and SQLite.","breadcrumb":{"@id":"https:\/\/www.sqltutorial.org\/sql-list-all-tables\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqltutorial.org\/sql-list-all-tables\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqltutorial.org\/sql-list-all-tables\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqltutorial.org\/"},{"@type":"ListItem","position":2,"name":"SQL List All Tables"}]},{"@type":"WebSite","@id":"https:\/\/www.sqltutorial.org\/#website","url":"https:\/\/www.sqltutorial.org\/","name":"SQL Tutorial","description":"An Interactive SQL Tutorial","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqltutorial.org\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"}]}},"_links":{"self":[{"href":"https:\/\/www.sqltutorial.org\/wp-json\/wp\/v2\/pages\/1185","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqltutorial.org\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/www.sqltutorial.org\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqltutorial.org\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqltutorial.org\/wp-json\/wp\/v2\/comments?post=1185"}],"version-history":[{"count":0,"href":"https:\/\/www.sqltutorial.org\/wp-json\/wp\/v2\/pages\/1185\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqltutorial.org\/wp-json\/wp\/v2\/media?parent=1185"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}