mysql提取5号的数据库_五、各类数据库信息的提取-优快云博客

本文链接：https://blog.youkuaiyun.com/weixin_28752627/article/details/113259727

为了后续做代码生成器，有必要了解下各类数据库信息的提取。一般需要提取的信息包括：数据库列表，存储过程，视图，数据表，数据表字段等。当然，类似SQLite这种数据库，需要提取的信息就相对很少了。

(一)SqlServer数据库信息提取

1.数据库列表获取：

--使用master系统数据库

USEmasterGO

--获取当前数据库列表

SELECT dbid,name FROM master..SysDatabases ORDER BY name ASC

--获取当前数据库列表并过滤系统数据库

SELECT dbid,name FROM master..SysDatabases WHERE name NOT IN('master','tempdb','model','msdb','ReportServer','ReportServerTempDB') ORDER BY name ASC

一般我们不需要获取到系统数据库，所以可以直接在SQL语句中过滤掉系统数据库。结果如下：

2.获取指定数据库存储过程

--使用指定数据库

USEDB_TestGO

--获取指定数据库存储过程

SELECT object_id,name,type FROM sys.objects WHERE type='p'

--获取指定数据库存储过程并过滤系统存储过程

SELECT object_id,name,type FROM sys.objects WHERE type='p' AND name NOT IN('sp_alterdiagram','sp_creatediagram','sp_dropdiagram','sp_helpdiagramdefinition','sp_helpdiagrams','sp_renamediagram','sp_upgraddiagrams') ORDER BY name ASC

在获取存储过程时需指明当前使用的数据库。执行结果如下：

3.获取指定数据库视图

--使用指定数据库

USEDB_TestGO

--获取指定数据库视图

SELECT object_id,name,type FROM sys.objects WHERE type='v' ORDER BY name ASC

视图和存储都可以使用系统objects表来获取，只要指明类型即可。

4.获取指定数据库下所有表

--使用指定数据库

USEDB_TestGO

--获取指定数据库下所有表

SELECT

[Id]=O.object_id, --编号

[Name]=O.name, --名称

[Desc]=ISNULL(EP.value,N'') --描述信息

FROMsys.objects OLEFT JOIN sys.extended_properties EP ON O.object_id=EP.major_id AND minor_id=0 AND class=1

WHERE O.type='U' AND O.name NOT IN('sysdiagrams','dtproperities')ORDER BY O.name ASC

获取的是指定数据库下所有表的编号、名称、和描述信息，并过滤掉系统表。执行结果如下：

如果需要获取单个表的描述信息，可以使用如下SQL语句：

--获取指定表的描述信息

SELECT

[Desc]=ISNULL(EP.value,N'')FROM sys.extended_properties EP INNER JOIN sys.objects O ON EP.major_id=O.object_id

WHERE minor_id=0 AND class=1 AND O.name='SaleOrderType' --指定表名称

执行结果如下：

5.获取指定数据表的字段信息

--使用指定数据库

USEDB_TestGO

--获取指定表字段信息

SELECT

[Id]=C.column_id, --编号

[Name]=C.name, --名称

[Type]=T.name, --类型

[Length]=C.max_length, --长度

[Identity]=CASE WHEN C.is_identity=1 THEN N'T'ELSE N'' END, --是否自增

[PrimaryKey]=ISNULL(PKInfo.PrimaryKey,N''), --是否是主键

[ForeignKey]=CASE WHEN FKInfo.parent_column_id>0 THEN N'T'ELSE N'' END, --外键

[ForeignKeyTable]=ISNULL(FKInfo.name,N''), --外键关联表名称

[IsNull]=CASE WHEN C.is_nullable=1 THEN N'T'ELSE N'' END, --是否可为空

[Default]=ISNULL(DC.definition,N''), --默认值

[ColumnDesc]=ISNULL(EP.value,N'') --描述信息

FROMsys.columns CINNER JOIN sys.objects O ON C.object_id=o.object_id AND O.type='U' AND O.is_ms_shipped=0

INNER JOIN sys.types T ON C.user_type_id=T.user_type_idLEFT JOIN sys.default_constraints DC ON C.object_id=DC.parent_object_id AND C.column_id=DC.parent_column_id AND C.default_object_id=DC.object_id

LEFT JOIN sys.extended_properties EP ON EP.class=1 AND C.object_id=EP.major_id AND C.column_id=EP.minor_idLEFT JOIN (SELECT IC.object_id,IC.column_id,PrimaryKey=CASE WHEN I.is_primary_key=1 THEN N'T'ELSE N'' END FROM sys.indexes I INNER JOIN sys.index_columns IC ON I.[object_id]=IC.[object_id] AND I.index_id=IC.index_id)PKInfo ON PKInfo.object_id=C.object_id AND PKInfo.column_id=C.column_idLEFT JOIN (SELECT FKC.parent_object_id,FKC.parent_column_id,O.name FROM sys.foreign_key_columns FKC INNER JOIN sys.objects O ON FKC.referenced_object_id=O.object_id)FKInfo ON C.object_id=FKInfo.parent_object_id AND C.column_id=FKInfo.parent_column_idWHERE O.name='Base_Person_Rank' --指定表名称

ORDER BY Id ASC

看了感觉很复杂，之所以复杂因为需要获取到外键表的部分信息，增加了获取表结构的难度。这个表结构的提取SQL应该算是最全的了。执行效果如下：

(二)MySql数据库信息提取

1.获取数据库列表

show databases;

MySql的数据库信息提取相对SqlServer来说要简便许多，但是能够干涉的成份也很少。

2.获取指定数据库存储过程(如：db_test)

select name,type from mysql.proc where db='db_test' and type='PROCEDURE' order by name asc;

3.获取指定数据库视图

select TABLE_NAME from information_schema.views where TABLE_SCHEMA='db_test' order by TABLE_NAME asc;

4.获取指定数据库下所有表

show tables;

获取指定表的描述信息(如：person)：

show table status where name='person';

5.获取指定表的字段信息(如：person)

show full fields from person;

执行结果如下：

(三)Oracle数据库信息提取

1.获取数据库空间列表

SELECT GLOBAL_NAME FROM GLOBAL_NAME;

2.获取指定空间下所有存储过程

SELECT OBJECT_NAME FROM USER_PROCEDURES;

3.获取指定空间下所有视图

SELECT VIEW_NAME FROM USER_VIEWS;

4.获取指定空间下所有的数据表

SELECT TABLE_NAME FROM User_TABLES ORDER BY TABLE_NAME ASC;

获取指定表的描述信息

SELECT COMMENTS FROM USER_TAB_COMMENTS WHERE TABLE_NAME='PERSON';

5.获取指定表(如：PERSON表)所有字段信息

SELECTUTC.TABLE_NAME,

UTC.COLUMN_ID,

UTC.COLUMN_NAME,

UTC.DATA_TYPE,

UTC.DATA_LENGTH,

UTC.DATA_DEFAULT,

UTC.NULLABLE,

UCC.COMMENTS,

TB_CON.CONSTRAINT_TYPEFROMUSER_TAB_COLUMNS UTCINNER JOIN USER_COL_COMMENTS UCC ON UTC.COLUMN_NAME=UCC.COLUMN_NAMELEFT JOIN(SELECTUCC.COLUMN_NAME,

UC.CONSTRAINT_NAME,

UC.CONSTRAINT_TYPEFROMUSER_CONS_COLUMNS UCCINNER JOIN USER_CONSTRAINTS UC ON UCC.CONSTRAINT_NAME=UC.CONSTRAINT_NAMEWHERE UC.TABLE_NAME='PERSON' AND (UC.CONSTRAINT_TYPE='R' OR UC.CONSTRAINT_TYPE='P')

)TB_CONON UTC.COLUMN_NAME=TB_CON.COLUMN_NAMEWHERE UTC.TABLE_NAME='PERSON' AND UCC.TABLE_NAME='PERSON'

ORDER BY UTC.COLUMN_ID ASC;

执行结果如下：

(四)SQLite数据库信息提取

1.获取指定文件中所有视图

select name from sqlite_master where type='view' order by name asc;

2.获取指定文件中所有数据表

select name from sqlite_master where type='table' order by name asc;

3.获取指定表字段信息(如：PERSON表)

pragma table_info('PERSON');

执行结果如下：

到此一些基本的主流数据库提取信息的方法已经列出，但上述方式也仅供参考并不是最完善的解决方案。如果有更好的方式可以在下方留言。