Difference between revisions of "What are my tables?"

From SQLZOO
Jump to navigation Jump to search
 
Line 24: Line 24:
 
<source lang='sql' class='def e-oracle'>SELECT * FROM cat
 
<source lang='sql' class='def e-oracle'>SELECT * FROM cat
 
</source>
 
</source>
<source lang='sql' class='def e-sqlserver'>SELECT * FROM sysobjects
+
<source lang='sql' class='def e-mssql'>SELECT * FROM sysobjects
 
WHERE xtype='U'
 
WHERE xtype='U'
 
</source>
 
</source>
Line 38: Line 38:
 
<div class="ecomm e-access" style="display: none">From within Access the system tables can be viewed by ticking the appropriate box under Tools/Options/View.</div>
 
<div class="ecomm e-access" style="display: none">From within Access the system tables can be viewed by ticking the appropriate box under Tools/Options/View.</div>
 
<div class="ecomm e-oracle" style="display: none">See also user_tables and user_catalog.</div>
 
<div class="ecomm e-oracle" style="display: none">See also user_tables and user_catalog.</div>
<div class="ecomm e-sqlserver" style="display: none">See also sp_table and table sysobjects.</div>
+
<div class="ecomm e-mssql" style="display: none">See also sp_table and table sysobjects.</div>
 
</div>
 
</div>
 
{{Meta Data ref}}
 
{{Meta Data ref}}

Latest revision as of 17:23, 1 July 2017

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'