sqlserver、mysql、oracle查询表和列

sqlserver

查询一个表的所有列名，数据类型

select column_name,data_type from information_schema.columns 
where table_name = 'Sys_User'

• information_schema：系统视图

• sys.extended_properties：系统视图

--查询数据库所有表名

select name from sysobjects where xtype='u'


--查询数据库中所有表

select * from sys.tables
 

--查询数据库所有表名及每个表的行数

SELECT a.name, b.rows FROM sysobjects AS a 

INNER JOIN sysindexes AS b ON a.id = b.id

WHERE (a.type = 'u') AND (b.indid IN (0, 1))

ORDER BY a.name,b.rows DESC
 

--查询所有的表名及空间占用量\行数

select

object_name(id) tablename,

8*reserved/1024 reserved,

rtrim(8*dpages)+'kb' used,

8*(reserved-dpages)/1024 unused,

8*dpages/1024-rows/1024*minlen/1024 free,

rows

--,*

from sysindexes

where indid=1

order by tablename,reserved desc

 

mysql

查询所有表和对应的字段属性

SELECT
    TABLE_SCHEMA AS '库名',
    TABLE_NAME AS '表名',
    COLUMN_NAME AS '列名',
    ORDINAL_POSITION AS '列的排列顺序',
    COLUMN_DEFAULT AS '默认值',
    IS_NULLABLE AS '是否为空',
    DATA_TYPE AS '数据类型',
    CHARACTER_MAXIMUM_LENGTH AS '字符最大长度',
    NUMERIC_PRECISION AS '数值精度(最大位数)',
    NUMERIC_SCALE AS '小数精度',
    COLUMN_TYPE AS 列类型,
    COLUMN_KEY 'KEY',
    EXTRA AS '额外说明',
    COLUMN_COMMENT AS '注释'
FROM
    information_schema.`COLUMNS`
WHERE
    -- aaa为库名
    TABLE_SCHEMA = 'aaa'
ORDER BY
    TABLE_NAME,
    ORDINAL_POSITION;

mysql查询所有数据库表名和表注释

SELECT table_name, table_comment
FROM information_schema.tables
WHERE table_schema = '数据库名';

oracl

获取表

select table_name from user_tables; //当前用户拥有的表      
 
select table_name from all_tables; //所有用户的表
select table_name from dba_tables; //包括系统表
select table_name from dba_tables where owner='用户名'
 
-- user_tables：
-- table_name,tablespace_name,last_analyzed等
-- dba_tables：
-- ower,table_name,tablespace_name,last_analyzed等
-- all_tables：
-- ower,table_name,tablespace_name,last_analyzed等
-- all_objects：
-- ower,object_name,subobject_name,object_id,created,last_ddl_time,timestamp,status等

获取表字段

select * from user_tab_columns where Table_Name='用户表';
select * from all_tab_columns where Table_Name='用户表';
select * from dba_tab_columns where Table_Name='用户表';
-- user_tab_columns：
-- table_name,column_name,data_type,data_length,data_precision,data_scale,nullable,column_id等
-- all_tab_columns ：
-- ower,table_name,column_name,data_type,data_length,data_precision,data_scale,nullable,column_id等
-- dba_tab_columns：
-- ower,table_name,column_name,data_type,data_length,data_precision,data_scale,nullable,column_id等

获取表注释

select * from user_tab_comments
-- user_tab_comments：table_name,table_type,comments
-- 相应的还有dba_tab_comments，all_tab_comments，这两个比user_tab_comments多了ower列。
-- 获取字段注释：
select * from user_col_comments
-- user_col_comments：table_name,column_name,comments
-- 相应的还有dba_col_comments，all_col_comments，这两个比user_col_comments多了ower列。