MySQL、SQL server 、Oracle数据库中查询所有的数据库，查询指定数据库所有表名，查询所有的字段的名字

MySQL中查询所有数据库名和表名1.查询所有数据库show databases;2.查询指定数据库中所有表名select table_name from information_schema.tables where table_schema='database_name' and table_type='base table';3.查询指定表中的所有字段名select col...

艾欧尼亚归我了

11425人浏览 · 2019-06-22 12:40:20

艾欧尼亚归我了 · 2019-06-22 12:40:20 发布

MySQL中查询所有数据库名和表名

1.查询所有数据库
show databases;

2.查询指定数据库中所有表名
select table_name from information_schema.tables where table_schema='database_name' and table_type='base table';

3.查询指定表中的所有字段名
select column_name from information_schema.columns where table_schema='database_name' and table_name='table_name';

4.查询指定表中的所有字段名和字段类型
select column_name,data_type from information_schema.columns where table_schema='database_name' and table_name='table_name';

#######################################################################

SQLServer中查询所有数据库名和表名

1、查询数据库中的所有数据库名：
SELECT * FROM Master..SysDatabases ORDER BY Name;

2、查询当前数据库所有表名：
SELECT * FROM SysObjects Where XType='U'   ORDER BY Name;
  --XType='U':表示所有用户表;
  --XType='S':表示所有系统表;

3.查询某个表的所有字段名：
SELECT * FROM SysColumns WHERE id=Object_Id('TableName');

4.查询指定表中的所有字段名和字段类型
select sc.name,st.name from syscolumns sc,systypes st where sc.xtype=st.xtype and sc.id in(select id from sysobjects where xtype='U' and name='table_name');

sqlserver中各个系统表的作用
sysaltfiles 主数据库保存数据库的文件
syscharsets 主数据库字符集与排序顺序
sysconfigures 主数据库配置选项
syscurconfigs 主数据库当前配置选项
sysdatabases 主数据库服务器中的数据库
syslanguages 主数据库语言
syslogins 主数据库登陆帐号信息
sysoledbusers 主数据库链接服务器登陆信息
sysprocesses 主数据库进程
sysremotelogins主数据库远程登录帐号
syscolumns 每个数据库列
sysconstrains 每个数据库限制
sysfilegroups 每个数据库文件组
sysfiles 每个数据库文件
sysforeignkeys 每个数据库外部关键字
sysindexs 每个数据库索引
sysmenbers 每个数据库角色成员
sysobjects 每个数据库所有数据库对象
syspermissions 每个数据库权限
systypes 每个数据库用户定义数据类型

----1. 获取所有的数据库名-----

SELECT NAME FROM MASTER.DBO.SYSDATABASES ORDER BY NAME

-----2. 获取所有的表名------

SELECT NAME FROM SYSOBJECTS WHERE XTYPE='U' ORDER BY NAME

--XTYPE='U':表示所有用户表;

--XTYPE='S':表示所有系统表;

SELECT NAME FROM SYSOBJECTS WHERE TYPE = 'U' AND SYSSTAT = '83'

----注意：一般情况只需要TYPE = 'U'，但有时候会有系统表混在其中（不知道什么原因），加上后面一句后就能删除这些系统表了。

----3.获取所有字段名-----

SELECT NAME FROM SYSCOLUMNS WHERE ID=OBJECT_ID('BCUSTOMER');

SELECT SYSCOLUMNS.NAME,SYSTYPES.NAME,SYSCOLUMNS.ISNULLABLE,SYSCOLUMNS.LENGTH FROM SYSCOLUMNS, SYSTYPES

WHERE SYSCOLUMNS.XUSERTYPE = SYSTYPES.XUSERTYPE AND SYSCOLUMNS.ID = OBJECT_ID('BCUSTOMER');

/*注意点：

----（A）这里为了重点突出某些重要内容，选取了其中几项信息输出。

（B）SYSCOLUMNS表中只含有数据类型编号，要获取完整的名字需要从SYSTYPES表中找，一般用户使用的数据类型用XUSERTYPE对应比较好，不会出现一对多的情况。

（C）SYSCOLUMNS.LENGTH得到的是物理内存的长度，所以NVARCHAR和VARCHAR等类型在数据库中的显示是这个的一半。*/

----4、得到表中主键所包含的列名------

SELECT SYSCOLUMNS.NAME ,SYSOBJECTS.XTYPE

FROM SYSCOLUMNS,SYSOBJECTS,SYSINDEXES,SYSINDEXKEYS

WHERE SYSCOLUMNS.ID = OBJECT_ID('BCUSTOMER')

AND SYSOBJECTS.XTYPE = 'PK'

AND SYSOBJECTS.PARENT_OBJ = SYSCOLUMNS.ID

AND SYSINDEXES.ID = SYSCOLUMNS.ID

AND SYSOBJECTS.NAME = SYSINDEXES.NAME

AND SYSINDEXKEYS.ID = SYSCOLUMNS.ID

AND SYSINDEXKEYS.INDID = SYSINDEXES.INDID

AND SYSCOLUMNS.COLID = SYSINDEXKEYS.COLID;

/*注意：这是在4张系统表中寻找的，关系比较复杂，大致可以表示为：

SYSCOLUMNS中存有表中的列信息和表ID，

SYSOBJECTS表中存有主键名字（即PK_TABLE类似）和表ID，

SYSINDEXES中存有主键名字和表ID和INDEX编号，

SYSINDEXKEYS中存有表ID和INDEX编号和列编号，

一项一项对应起来后就能找到列名了，呼～*/

----5、得到表中列的描述内容-----

'表示没有找到'

SELECT A.NAME,G.VALUE

FROM SYSCOLUMNS AS A

LEFT JOIN SYSPROPERTIES G

ON A.ID=G.ID

AND A.COLID = G.SMALLID

WHERE A.ID='BCUSTOMER'

查询表结构信息：

SELECT (case when a.colorder=1 then d.name else null end) 表名,
a.colorder 字段序号,a.name 字段名,
(case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end) 标识,
(case when (SELECT count(*) FROM sysobjects
WHERE (name in (SELECT name FROM sysindexes
WHERE (id = a.id) AND (indid in
(SELECT indid FROM sysindexkeys
WHERE (id = a.id) AND (colid in
(SELECT colid FROM syscolumns WHERE (id = a.id) AND (name = a.name)))))))
AND (xtype = 'PK'))>0 then '√' else '' end) 主键,b.name 类型,a.length 占用字节数,
COLUMNPROPERTY(a.id,a.name,'PRECISION') as 长度,
isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as 小数位数,(case when a.isnullable=1 then '√'else '' end) 允许空,
isnull(e.text,'') 默认值,isnull(g.[value], ' ') AS [说明]
FROM syscolumns a
left join systypes b on a.xtype=b.xusertype
inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'
left join syscomments e on a.cdefault=e.id
left join sys.extended_properties g on a.id=g.major_id AND a.colid=g.minor_id
left join sys.extended_properties f on d.id=f.class and f.minor_id=0
where b.name is not null
--WHERE d.name='要查询的表' --如果只查询指定表,加上此条件
order by a.id,a.colorder

########仅查看非系统库信息

SELECT
表名 = case when a.colorder=1 then d.name else '' end,
表说明 = case when a.colorder=1 then isnull(f.value,'') else '' end,
字段序号 = a.colorder,
字段名 = a.name,
标识 = case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end,
主键 = case when exists(SELECT 1 FROM sysobjects where xtype='PK' and parent_obj=a.id and name in (
SELECT name FROM sysindexes WHERE indid in( SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid))) then '√' else '' end,
类型 = b.name,
占用字节数 = a.length,
长度 = COLUMNPROPERTY(a.id,a.name,'PRECISION'),
小数位数 = isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),
允许空 = case when a.isnullable=1 then '√'else '' end,
默认值 = isnull(e.text,''),
字段说明 = isnull(g.[value],'')
FROM
syscolumns a
left join
systypes b
on
a.xusertype=b.xusertype
inner join
sysobjects d
on
a.id=d.id and d.xtype='U' and d.name<>'dtproperties'
left join
syscomments e
on
a.cdefault=e.id
left join
sys.extended_properties g
on
a.id=G.major_id and a.colid=g.minor_id
left join
sys.extended_properties f
on
d.id=f.major_id and f.minor_id=0
--where
-- d.name='Sys_User' --如果只查询指定表,加上此where条件，tablename是要查询的表名；去除where条件查询所有的表信息
order by
a.id,a.colorder

###############################################################3

Oracle中查询所有数据库名和表名

1.查询所有数据库
由于Oralce没有库名,只有表空间,所以Oracle没有提供数据库名称查询支持，只提供了表空间名称查询。
select * from v$tablespace;--查询表空间(需要一定权限)

2.查询当前数据库中所有表名
select * from user_tables;

3.查询指定表中的所有字段名
select column_name from user_tab_columns where table_name = 'table_name';--表名要全大写

4.查询指定表中的所有字段名和字段类型
select column_name, data_type from user_tab_columns where table_name = 'table_name';--表名要全大写

5.获取表：

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等

6.获取表字段

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等

7.获取表注释

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列。

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

转自：https://blog.csdn.net/lanxingbudui/article/details/83009497

https://www.cnblogs.com/lbg280/p/6256406.html

https://www.cnblogs.com/qianjinyan/p/9636535.html