查找数据库中的所有字段的信息

查找数据库中的所有字段

近期，小黄在工作中接到了一项任务

因为要做一个数据分析系统，所有要求整理我们所有系统所在的数据库需要显示以下字段

数据库名、表名、字段名、字段类型、是否主键、是否索引、备注

这可把SQL功底不扎实的小黄给难坏了

我们先来分析一下需求

• 这些数据在我们平时所使用的表中完全查找不到
• 从百度上打探到，数据库都对应的系统表（这个信息小黄竟然还是第一次了解到！！）
• 所以我们应该从系统表下手

公司的系统的数据库部署在不同的数据库上面，涉及到的有MySQL、Oracle、SQL server

---

MySQL解决方案

小黄第一想法就是先解决最常用的MySQL

说句后话，MySQL数据库是处理起来最简单的

MySQL数据库在创建的时候创建了以下4个数据库

在information_schema中可以找到我们所需要的数据

这里需要注意的是主键信息，他会给你返回四种情况

• PRI：该列是主键的组成部分
• MUL：该列的值可以重复, 该列是一个非唯一索引的前导列(第一列)或者是一个唯一性索引的组成部分但是可以含有空值NULL
• UNI：该列是一个唯一值索引的第一列(前导列),并别不能含有空值(NULL)
• 空的：该列值的可以重复, 表示该列没有索引, 或者是一个非唯一的复合索引的非前导列

以上主键信息解释转载于https://www.cnblogs.com/licheng/archive/2010/10/16/1852938.html

到此，我们只剩下一个索引没有解决

以上数据我们都找到了，接下来就是写SQL让他们合并在一起

select
	ic.table_schema '数据库名',
	ic.table_name '表名',
	ic.column_name '列名',
	ic.data_type '字段类型',
case ic.column_key
	when 'PRI' then
		'是'
	else 
		'否'
end '是否主键',
IF(ist.column_name = ic.column_name,'是','否')	'是否索引',
	ic.column_comment
from 
	information_schema.columns  ic	-- 字段表
left join
	information_schema.statistics ist  -- 索引
on	
	(ic.table_schema = ist.table_schema
and
	ic.table_name = ist.table_name)

---

Oracle解决方案

MySQL相对来说简单的原因是他是可视化操作，而Oracle我们需要输入命令来查找系统表

select * from user_tab_columns	--这条语句可以找出类似于MySQL中columns的表

这里有一个注意点（user_tab_columns中的user）

• user：可以查看某一用户所拥有的所有对象
• dba：可以查看所有对象（前提是该用户拥有dba权限）
• all：可以查看某一用户拥有的或可以访问的所有对象

这样我们可以获得表名、字段名、数据类型（这边没有获取数据库名是因为我们实际过程中当前用户只能访问一个数据库并且我们平时用的也是该数据库，所以数据库名另外加上即可）

select * from  user_cons_columns

这一块代表的是约束信息，这里1234显示的应该是顺序，而我观察了我这边的数据库可以得到‘1’是主键

接下来我们需要查找索引，使用以下语句

select * from user_IND_COLUMNS

可以通过外连接将其显示

到此为止Oracle我们只剩下一个备注还没有搞定，通过下面这条语句可以找到字段对应的备注信息

select * from user_col_comments

至此我们已经找到了所有需要的数据，接下来只需执行sql语句拼接即可

select
	dtc.table_name table_name,
	dtc.column_name column_name,
	dtc.data_type column_type,
CASE ucc.position
	WHEN 1 THEN
		'是'
	ELSE
		'否'
END iskey,
CASE  
	WHEN uic.COLUMN_POSITION is null THEN
		'否'
	ELSE
		'是'
END isindex,
	utc.COMMENTS

from
	(select * from user_tab_columns) dtc
LEFT JOIN
	(select 
	*
	from  user_cons_columns col
	WHERE col.POSITION = 1) ucc
on 
	(dtc.table_name = ucc.table_name and dtc.column_name = ucc.column_name)
LEFT JOIN	
	(select * from user_IND_COLUMNS) uic
on 
	(dtc.table_name = uic.table_name and dtc.column_name = uic.column_name)
LEFT JOIN
	(select * from user_col_comments) utc
on
	(dtc.table_name = utc.table_name and dtc.column_name = utc.column_name)
ORDER BY
	dtc.table_name

---

SQL server解决方案

说来惭愧，小黄至今都还未解决SQL server中索引和备注的信息，主要原因是查找到了索引但不知道如何将两张表连接在一起。在这里小黄也希望各位大佬来帮我诊断一下以上是否有错误，以及帮我补充一下SQL server的解决方案。

select 	* from INFORMATION_SCHEMA.COLUMNS --这条语句作为我们的主表，其他的表进行外连接

查找字段是否为主键

select 	*  from INFORMATION_SCHEMA.KEY_COLUMN_USAGE	

SQL server我的部分解决方案

select 
	tsc.table_catalog,
	tsc.table_name,
	tsc.column_name,
	tsc.data_type,
CASE 
	WHEN iskc.ordinal_position is null 
	THEN '否'
	
	ELSE '是'
END iskey
from INFORMATION_SCHEMA.COLUMNS tsc

left join
	(select 	*  from INFORMATION_SCHEMA.KEY_COLUMN_USAGE) iskc
on 
	(tsc.column_name = iskc.column_name and tsc.table_name = iskc.table_name)

order by table_name

---

2021.8.9更新

小黄来更新啦！至今还没有解决SQL server索引的问题，但又遇到一个新的问题，简直就是一波未平一波又起！！！
情况大致就是上面这么个情况，多了一个系统使用的是pgsql的数据管理系统，这可把小黄整懵了，上述三个数据系统大多都是有所耳闻，这个是我见识短浅，听都没听过。还是面向百度解决问题，让我们看看小黄是如何解决的

pgsql解决方案

小黄经过一早上的努力还是完全解决，没办法将索引表和字段表关联，希望大佬能帮我解决以下
总结一下经验，我们主表一般都选择用字段来呈现，所以我们考虑查询一下字段信息

SELECT * FROM pg_attribute

调用以上方法可以得到数据库中所有的字段

那我们可以重新理一下sql语句

SELECT * FROM pg_attribute where attnum > 0

接下来我们先将表ID进行关联，以下是查找表的代码
这里要着重讲一下系统字段，在字段表和表名表进行关联的时候
关联条件是 字段表.attrelid = 表名表.oid
而oid是需要显示定义的，直接查询时无法显示系统字段的

SELECT 
	C.oid,* 
FROM
	pg_class C 
WHERE
	relkind = 'r' 
	AND relname NOT LIKE'pg_%' 
	AND relname NOT LIKE'sql_%'

这里需要注意的时relking字段，我们可以来看一下官方文档pg_class官方文档
我们需要的是普通表，所以将值为r的筛选出来
而表名中pg开头的值和sql开头的值对应的都是系统表，我们也不需要

接下来我们需要解决的是字段类型

SELECT 	t.oid,* FROM 	pg_type  t

再者就是解决字段备注的语句,调用以下方法

SELECT * FROM 	pg_description   d

pgsql的部分解决方案

SELECT 
	C.relname 表名,
	cast(obj_description(relfilenode,'pg_class') as varchar) AS "表名描述",
  A.attname 字段名,
	t.typname as "字段类型",
	d.description AS "字段备注",
	(case
		when (
		select
			count(pg_constraint.*)
		from
			pg_constraint
		inner join pg_class on
			pg_constraint.conrelid = pg_class.oid
		inner join pg_attribute on
			pg_attribute.attrelid = pg_class.oid
			and pg_attribute.attnum = any(pg_constraint.conkey)
		inner join pg_type on
			pg_type.oid = pg_attribute.atttypid
		where
			pg_class.relname = c.relname
			and pg_constraint.contype = 'p'
			and pg_attribute.attname = a.attname) > 0 then true
		else false end) as 是否主键
FROM
	pg_attribute A 
LEFT JOIN 
	( SELECT c.oid,* FROM pg_class C WHERE relkind = 'r' AND relname NOT LIKE'pg_%' AND relname NOT LIKE'sql_%' ) C
ON 
	A.attrelid = C.oid 
left join 
	(SELECT 	t.oid,* FROM 	pg_type  t) T
on
	a.atttypid = t.oid
left join 
  (SELECT * FROM 	pg_description   d) D
on 
	d.objoid=a.attrelid and d.objsubid=a.attnum
WHERE
	c.relname is not null
and
	a.attnum > 0
order by 
  C.relname

以上就是小黄的解决方案，欢迎各位补充！！！