五、各类数据库信息的提取

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

（一）SqlServer数据库信息提取

1.数据库列表获取：

--使用master系统数据库
USE master
GO

--获取当前数据库列表
SELECT dbid,name FROM master..SysDatabases ORDER BY name ASC
GO

--获取当前数据库列表并过滤系统数据库
SELECT dbid,name FROM master..SysDatabases WHERE name NOT IN('master','tempdb','model','msdb','ReportServer','ReportServerTempDB') ORDER BY name ASC
GO

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

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

--使用指定数据库
USE DB_Test
GO

--获取指定数据库存储过程
SELECT object_id,name,type FROM sys.objects WHERE type='p'
GO

--获取指定数据库存储过程并过滤系统存储过程
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
GO

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

3.获取指定数据库视图

--使用指定数据库
USE DB_Test
GO

--获取指定数据库视图
SELECT object_id,name,type FROM sys.objects WHERE type='v' ORDER BY name ASC
GO

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

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

--使用指定数据库
USE DB_Test
GO

--获取指定数据库下所有表
SELECT 
[Id]=O.object_id, --编号
[Name]=O.name,    --名称
[Desc]=ISNULL(EP.value,N'')  --描述信息
FROM sys.objects O 
LEFT 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
GO

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

如果需要获取单个表的描述信息，可以使用如下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' --指定表名称
GO

执行结果如下：

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

--使用指定数据库
USE DB_Test
GO

--获取指定表字段信息
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'')     --描述信息
FROM sys.columns C 
INNER 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_id
LEFT 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_id
LEFT 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_id
LEFT 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_id
WHERE 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表）所有字段信息

SELECT 
UTC.TABLE_NAME,
UTC.COLUMN_ID,
UTC.COLUMN_NAME,
UTC.DATA_TYPE,
UTC.DATA_LENGTH,
UTC.DATA_DEFAULT,
UTC.NULLABLE,
UCC.COMMENTS,
TB_CON.CONSTRAINT_TYPE
FROM USER_TAB_COLUMNS UTC
INNER JOIN USER_COL_COMMENTS UCC ON UTC.COLUMN_NAME=UCC.COLUMN_NAME
LEFT JOIN(
SELECT
UCC.COLUMN_NAME,
UC.CONSTRAINT_NAME,
UC.CONSTRAINT_TYPE 
FROM USER_CONS_COLUMNS UCC
INNER JOIN USER_CONSTRAINTS UC ON UCC.CONSTRAINT_NAME=UC.CONSTRAINT_NAME 
WHERE UC.TABLE_NAME='PERSON' AND (UC.CONSTRAINT_TYPE='R' OR UC.CONSTRAINT_TYPE='P') 
)TB_CON ON UTC.COLUMN_NAME=TB_CON.COLUMN_NAME
WHERE 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');

执行结果如下：

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