189 lines
9.3 KiB
Java
189 lines
9.3 KiB
Java
import java.io.*;
|
||
import java.nio.charset.StandardCharsets;
|
||
import java.sql.*;
|
||
import java.util.*;
|
||
|
||
/**
|
||
* 导出该 SQL Server 实例上任意数据库的用户表与视图结构为 Markdown 文档。
|
||
*
|
||
* 典型用途：
|
||
* - 导出旧库 G3HY2025 的表结构（历史对照）
|
||
* - 导出新库 FMS 的表结构（V2 核心表 / 业务表 / 视图）
|
||
* 新库的 s_module.b_view_table 大多指向视图，因此视图与表一并导出，
|
||
* 视图标注为「视图」且无主键信息。
|
||
*
|
||
* 用法：
|
||
* javac -encoding UTF-8 -cp "mssql-jdbc-13.4.0.jre11.jar" SchemaDumpMarkdown.java
|
||
* java -Dstdout.encoding=UTF-8 -cp ".;mssql-jdbc-13.4.0.jre11.jar" SchemaDumpMarkdown <输出文件绝对路径> [数据库名，默认 G3HY2025] [--tables-only]
|
||
*
|
||
* --tables-only 只导出表，不含视图（旧行为）
|
||
*/
|
||
public class SchemaDumpMarkdown {
|
||
|
||
static final String BASE_URL = "jdbc:sqlserver://118.89.70.199:1433;encrypt=true;trustServerCertificate=true";
|
||
static final String USER = "sa";
|
||
static final String PASSWORD = "zhang520..";
|
||
|
||
static class Col {
|
||
String name, type, len, precScale, nullable, def;
|
||
}
|
||
|
||
public static void main(String[] args) throws Exception {
|
||
boolean tablesOnly = false;
|
||
List<String> positional = new ArrayList<>();
|
||
for (String raw : args) {
|
||
String arg = raw.trim();
|
||
if (arg.equalsIgnoreCase("--tables-only")) {
|
||
tablesOnly = true;
|
||
} else {
|
||
positional.add(arg);
|
||
}
|
||
}
|
||
|
||
String out = positional.size() > 0 ? positional.get(0) : "Fms旧系统表结构.md";
|
||
String db = positional.size() > 1 ? positional.get(1) : "G3HY2025";
|
||
String url = BASE_URL + ";databaseName=" + db;
|
||
|
||
Map<String, List<Col>> cols = new LinkedHashMap<>();
|
||
Map<String, String> pk = new LinkedHashMap<>();
|
||
Map<String, List<String>> fk = new LinkedHashMap<>();
|
||
List<String> tables = new ArrayList<>();
|
||
List<String> views = new ArrayList<>();
|
||
|
||
try (Connection conn = DriverManager.getConnection(url, USER, PASSWORD)) {
|
||
// 对象清单（表在前，视图在后）
|
||
try (Statement st = conn.createStatement();
|
||
ResultSet rs = st.executeQuery("SELECT TABLE_NAME, TABLE_TYPE FROM INFORMATION_SCHEMA.TABLES "
|
||
+ "WHERE TABLE_TYPE IN ('BASE TABLE','VIEW') ORDER BY TABLE_TYPE, TABLE_NAME")) {
|
||
while (rs.next()) {
|
||
if ("VIEW".equalsIgnoreCase(rs.getString(2))) views.add(rs.getString(1));
|
||
else tables.add(rs.getString(1));
|
||
}
|
||
}
|
||
|
||
// 字段（表与视图共用）
|
||
try (Statement st = conn.createStatement();
|
||
ResultSet rs = st.executeQuery("SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, "
|
||
+ "CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE, IS_NULLABLE, COLUMN_DEFAULT "
|
||
+ "FROM INFORMATION_SCHEMA.COLUMNS ORDER BY TABLE_NAME, ORDINAL_POSITION")) {
|
||
while (rs.next()) {
|
||
Col c = new Col();
|
||
c.name = rs.getString(2);
|
||
c.type = rs.getString(3);
|
||
int maxLen = rs.getInt(4);
|
||
c.len = rs.wasNull() ? "" : (maxLen == -1 ? "MAX" : String.valueOf(maxLen));
|
||
Object p = rs.getObject(5);
|
||
if (p != null) c.precScale = p + "," + rs.getObject(6);
|
||
else c.precScale = "";
|
||
c.nullable = rs.getString(7);
|
||
c.def = rs.getString(8);
|
||
cols.computeIfAbsent(rs.getString(1), k -> new ArrayList<>()).add(c);
|
||
}
|
||
}
|
||
|
||
// 主键（仅表有）
|
||
try (Statement st = conn.createStatement();
|
||
ResultSet rs = st.executeQuery("SELECT OBJECT_NAME(k.parent_object_id), c.name "
|
||
+ "FROM sys.key_constraints k "
|
||
+ "JOIN sys.index_columns ic ON ic.object_id = k.parent_object_id AND ic.index_id = k.unique_index_id "
|
||
+ "JOIN sys.columns c ON c.object_id = ic.object_id AND c.column_id = ic.column_id "
|
||
+ "WHERE k.type = 'PK' "
|
||
+ "ORDER BY 1, ic.key_ordinal")) {
|
||
while (rs.next()) {
|
||
String t = rs.getString(1);
|
||
pk.merge(t, rs.getString(2), (a, b) -> a + ", " + b);
|
||
}
|
||
}
|
||
|
||
// 外键（仅表有）
|
||
try (Statement st = conn.createStatement();
|
||
ResultSet rs = st.executeQuery("SELECT OBJECT_NAME(f.parent_object_id), "
|
||
+ "COL_NAME(fc.parent_object_id, fc.parent_column_id), "
|
||
+ "OBJECT_NAME(f.referenced_object_id), "
|
||
+ "COL_NAME(fc.referenced_object_id, fc.referenced_column_id) "
|
||
+ "FROM sys.foreign_keys f JOIN sys.foreign_key_columns fc ON f.object_id = fc.constraint_object_id "
|
||
+ "ORDER BY 1, 2")) {
|
||
while (rs.next()) {
|
||
fk.computeIfAbsent(rs.getString(1), k -> new ArrayList<>())
|
||
.add(rs.getString(2) + " -> " + rs.getString(3) + "(" + rs.getString(4) + ")");
|
||
}
|
||
}
|
||
|
||
List<String> listedViews = tablesOnly ? Collections.emptyList() : views;
|
||
|
||
StringBuilder sb = new StringBuilder();
|
||
sb.append("# 表结构导出 — ").append(db).append(" 库\n\n");
|
||
sb.append("> 来源库：`").append(db).append("`（").append(url).append("）\n");
|
||
sb.append("> 包含用户表").append(tablesOnly ? "" : "和视图")
|
||
.append("；字段「备注」列留空，供后续补充含义。").append("\n");
|
||
sb.append("> 拉取时间：").append(new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new java.util.Date())).append("\n");
|
||
sb.append("> 约束情况：该库定义了 ").append(fk.size()).append(" 张表的外键约束；")
|
||
.append(pk.size()).append(" 张表定义了主键，").append(tables.size() - pk.size())
|
||
.append(" 张表无主键。\n");
|
||
if (!tablesOnly) {
|
||
sb.append("> 视图无主键和默认值，字段来源见视图定义。\n");
|
||
}
|
||
sb.append("\n共 **").append(tables.size()).append("** 张表");
|
||
if (!tablesOnly) sb.append("、**").append(listedViews.size()).append("** 个视图");
|
||
sb.append("。\n\n");
|
||
|
||
sb.append("## 对象清单（索引）\n\n");
|
||
sb.append("| # | 名称 | 类型 | 字段数 | 主键 |\n| --- | --- | --- | --- | --- |\n");
|
||
int i = 1;
|
||
for (String t : tables) {
|
||
sb.append("| ").append(i++).append(" | `").append(t).append("` | 表 | ")
|
||
.append(cols.getOrDefault(t, Collections.emptyList()).size())
|
||
.append(" | ").append(esc(pk.getOrDefault(t, ""))).append(" |\n");
|
||
}
|
||
for (String v : listedViews) {
|
||
sb.append("| ").append(i++).append(" | `").append(v).append("` | 视图 | ")
|
||
.append(cols.getOrDefault(v, Collections.emptyList()).size())
|
||
.append(" | |\n");
|
||
}
|
||
|
||
sb.append("\n## 表字段结构\n");
|
||
for (String t : tables) {
|
||
appendObject(sb, t, pk, fk, cols);
|
||
}
|
||
|
||
if (!listedViews.isEmpty()) {
|
||
sb.append("\n## 视图字段结构\n");
|
||
for (String v : listedViews) {
|
||
appendObject(sb, v, pk, fk, cols);
|
||
}
|
||
}
|
||
|
||
try (Writer w = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(out), StandardCharsets.UTF_8))) {
|
||
w.write(sb.toString());
|
||
}
|
||
System.out.println("已写出 " + tables.size() + " 张表"
|
||
+ (listedViews.isEmpty() ? "" : "、" + listedViews.size() + " 个视图") + " -> " + out);
|
||
}
|
||
}
|
||
|
||
static void appendObject(StringBuilder sb, String name,
|
||
Map<String, String> pk, Map<String, List<String>> fk,
|
||
Map<String, List<Col>> cols) {
|
||
List<Col> cs = cols.getOrDefault(name, Collections.emptyList());
|
||
sb.append("\n### ").append(name).append("\n\n");
|
||
if (pk.containsKey(name)) sb.append("**主键：** ").append(esc(pk.get(name))).append("\n\n");
|
||
List<String> fs = fk.get(name);
|
||
if (fs != null && !fs.isEmpty()) {
|
||
sb.append("**外键：**\n");
|
||
for (String f : fs) sb.append("- ").append(esc(f)).append("\n");
|
||
sb.append("\n");
|
||
}
|
||
sb.append("| 字段名 | 类型 | 长度 | 精度,小数位 | 可空 | 默认值 | 备注 |\n");
|
||
sb.append("| --- | --- | --- | --- | --- | --- | --- |\n");
|
||
for (Col c : cs) {
|
||
sb.append("| `").append(c.name).append("` | ").append(c.type).append(" | ").append(c.len)
|
||
.append(" | ").append(c.precScale).append(" | ").append(c.nullable)
|
||
.append(" | ").append(c.def == null ? "" : esc(c.def)).append(" | |\n");
|
||
}
|
||
}
|
||
|
||
static String esc(String s) {
|
||
return s == null ? "" : s.replace("|", "\\|");
|
||
}
|
||
}
|