Difference between revisions of "What are my tables?"

From SQLZOO
Jump to: navigation, search
(Created page with "Get a list of all tables <div class='ht'> <div> We should expect to find a system table that includes a list of tables. We should expect this to contain a great deal of extra ...")
 
(5 intermediate revisions by the same user not shown)
Line 1: Line 1:
 
Get a list of all tables
 
Get a list of all tables
 
<div class='ht'>
 
<div class='ht'>
 +
<div class=params>schema:gisq</div>
 
<div>
 
<div>
 
We should expect to find a system table that includes a list of tables.
 
We should expect to find a system table that includes a list of tables.
 
We should expect this to contain a great deal of extra data that is hard to understand.  
 
We should expect this to contain a great deal of extra data that is hard to understand.  
 
</div>
 
</div>
<source lang=sql class='tidy'>CREATE TABLE bbc (name VARCHAR(10), region VARCHAR(10));
+
<source lang=sql class='tidy'>
INSERT INTO bbc VALUES ('Poland', 'Europe');
 
INSERT INTO bbc VALUES ('Japan', 'Asia');
 
 
</source>
 
</source>
 +
<source lang=sql class='setup'></source>
 
<source lang='sql' class='def e-sqlite'>SELECT * FROM sqlite_master
 
<source lang='sql' class='def e-sqlite'>SELECT * FROM sqlite_master
 
WHERE type='table'
 
WHERE type='table'
Line 40: Line 40:
 
<div class="ecomm e-sqlserver" style="display: none">See also sp_table and table sysobjects.</div>
 
<div class="ecomm e-sqlserver" style="display: none">See also sp_table and table sysobjects.</div>
 
</div>
 
</div>
{{Meta_Data Ref}}
+
{{Meta Data ref}}

Revision as of 14:39, 12 July 2012

Get a list of all tables

schema:gisq

We should expect to find a system table that includes a list of tables. We should expect this to contain a great deal of extra data that is hard to understand.

SELECT * FROM sqlite_master
WHERE type='table'
SELECT * FROM syscat.tables
WHERE tabschema = 'SCOTT'
SELECT Name FROM MSysObjects
WHERE Type=1 AND Flags=0
SELECT tablename FROM pg_tables
WHERE tableowner = current_user
SELECT * FROM cat
SELECT * FROM sysobjects
WHERE xtype='U'
show tables
SELECT * FROM information_schema.tables
WHERE table_type='BASE TABLE'
SELECT * FROM sysobjects
WHERE type='U'