Database Skill

Database Skill

About MySql, SQL Server, Oracle, PostgreSql, DB2, SyBase, SQL, T-SQL, PL-SQL.

List all user tables

Tag: SQL, table, user, database Category: Sql Author: fy290828562 Date: 2010-08-26

Sql --------------------

Can list all user tables: table names, field names, field types, field length :) summed up for all to share.
In the order listed, table names, field names, field types, field length
select o.name as tbna, c.name as colna, t.name as typena, c.length as lenna
from
syscolumns c inner join
sysobjects o on c.id = o.id and o.xtype = 'u' inner join
systypes t on c.xtype = t.xtype
Followed by the where, you can get the information of the specified table.
where o.name = 'specify the table name'

Oracle ---------------------------
SELECT table_name, COLUMN_NAME, DATA_TYPE, DATA_LENGTH FROM user_TAB_COLUMNS

select * from user_tables; / / See the current user all the information
select * from all_tables; / / View all the tables in the current database information

select * from user_tab_columns; / / view the current user table field
select * from all_tab_columns; / / View all table field names and other information

----------------------------------------

SELECT COLUMN_NAME, DATA_TYPE, DATA_LENGTH / / field name, data type, length,
FROM
(SELECT to_char (rownum) num, COLUMN_NAME, DATA_TYPE, DATA_LENGTH
FROM ALL_TAB_COLUMNS
WHERE table_name = 'BUILDING')
where num = 2
NOTE: Table capital letter