From 4370ec50a74d8bd14b8c509cf205946082493a91 Mon Sep 17 00:00:00 2001
From: yeg <1347747024@qq.com>
Date: 星期五, 09 十月 2026 13:15:14 +0800
Subject: [PATCH] 探查脚本改为只报结论：几千个库时不再全量罗列
---
scripts/j22-introspect.ps1 | 203 +++++++++++++++++++++++++++++++++++---------------
1 files changed, 140 insertions(+), 63 deletions(-)
diff --git a/scripts/j22-introspect.ps1 b/scripts/j22-introspect.ps1
index 8ee2a04..d8c95c4 100644
--- a/scripts/j22-introspect.ps1
+++ b/scripts/j22-introspect.ps1
@@ -1,26 +1,43 @@
<#
22 所上位机 SQL Server 只读探查：找出「工步方案」在哪里（工步名称可能只存在于方案里）。
- 与取数程序同一条纪律：只执行 SELECT，不写、不建、不改、不调用存储过程，每条查询都显式设超时。
- 甲方可自行审计本文件。
+ v2：那台实例有几千个库，全量罗列没法看，所以本版**只报结论**——不列库、不列表，
+ 只找"像方案的表"与"同时含工步列和名称列的表"，命中项才打印细节；其余只给计数。
- 为什么需要它：`Ws<N>_IV_0` 采样表里只有工步**号**，没有工步**名称**。
- 取数程序只遍历 `DB_` 开头的测试库，所以这台实例上**其他库我们从来没看过**——
- 方案的名称很可能就在那些库里。
+ 只读纪律与取数程序一致：只执行 SELECT，不写、不建、不改、不调用存储过程，每条查询都显式设超时。
+ 甲方可自行审计本文件。
用法（在那台机器上，Windows 认证免密码）：
powershell -ExecutionPolicy Bypass -File .\j22-introspect.ps1
powershell -ExecutionPolicy Bypass -File .\j22-introspect.ps1 -SqlInstance '.\SQLEXPRESS'
+ powershell -ExecutionPolicy Bypass -File .\j22-introspect.ps1 -Database 'SomeDb' # 只看指定库
#>
param(
[string]$SqlInstance = '.\SQLEXPRESS',
[int]$ConnectTimeoutSec = 10,
[int]$CommandTimeoutSec = 15,
- [int]$SampleRows = 3
+ # 非 DB_ 库最多探查多少个（按创建时间倒序，最早的先跳过）。几千个库时防止跑太久。
+ [int]$MaxDatabases = 100,
+ [int]$SampleRows = 3,
+ # 最多打印多少张命中表：命中太多就没法复制了，其余只计数。
+ [int]$MaxHits = 25,
+ [string]$Database = '',
+ [string]$ReportFile = ''
)
$ErrorActionPreference = 'Stop'
-# 只允许常规库名：库名会被拼进查询（标识符不能用参数），异常字符一律跳过，不改写厂商数据。
+# 报告同时写文件：控制台一次刷不完，文件可以慢慢复制。
+if (-not $ReportFile) {
+ $base = if ($PSScriptRoot) { $PSScriptRoot } else { (Get-Location).Path }
+ $ReportFile = Join-Path $base 'j22-introspect-report.txt'
+}
+$report = New-Object System.Collections.ArrayList
+function Say([string]$text) {
+ Write-Host $text
+ [void]$report.Add($text)
+}
+
+# 只允许常规库名/表名：库名与表名会被拼进查询（标识符不能用参数），异常字符一律跳过。
function Test-SafeName([string]$name) { return $name -match '^[A-Za-z0-9_$]+$' }
# 所有查询都经过这里：显式设 CommandTimeout，读满即断，不发加锁读、不开启事务。
@@ -44,99 +61,159 @@
finally { $reader.Close(); $command.Dispose() }
}
-function Write-Row($values) {
- $text = ($values | ForEach-Object { if ($null -eq $_) { '<null>' } else { [string]$_ } }) -join ' | '
- Write-Host (' ' + $text)
-}
+# 表名像"方案/配方/任务"的，以及同时含工步列与名称列的，都算候选。
+$planishTable = "(name like '%Step%' or name like '%Recipe%' or name like '%Task%' or name like '%Plan%'" +
+ " or name like '%Program%' or name like '%Scheme%' or name like '%Procedure%' or name like '%Formula%')"
+$stepishColumn = "(c.name like '%Step%' or c.name like '%工步%')"
+$nameishColumn = "(c.name like '%Name%' or c.name like '%Desc%' or c.name like '%Title%'" +
+ " or c.name like '%Remark%' or c.name like '%说明%' or c.name like '%名称%' or c.name like '%备注%')"
+$nameishLike = "(?i)(name|desc|title|remark|说明|名称|备注)"
$connectionString = "Server=$SqlInstance;Integrated Security=True;Connect Timeout=$ConnectTimeoutSec;" +
"Application Name=j22-introspect"
$connection = New-Object System.Data.SqlClient.SqlConnection $connectionString
$connection.Open()
-Write-Host "已连接：$SqlInstance（只读探查，不写任何数据）"
try {
- # ---------------------------------------------------------------- 1. 库清单
- Write-Host ''
- Write-Host '=== 1. 用户库清单（database_id > 4） ==='
- $databases = Invoke-ReadOnlyQuery $connection @'
+ Say "已连接：$SqlInstance（只读探查，不写任何数据）"
+ Say ''
+
+ # ---------------------------------------------------------------- 1. 要探查哪些库
+ $all = Invoke-ReadOnlyQuery $connection @'
select name, create_date
from sys.databases
where database_id > 4
-order by name
+order by create_date desc
'@
- $testDatabases = New-Object System.Collections.ArrayList
- $otherDatabases = New-Object System.Collections.ArrayList
- foreach ($row in $databases) {
- $name = [string]$row[0]
- if ($name -like 'DB[_]%') { [void]$testDatabases.Add($name) } else { [void]$otherDatabases.Add($row) }
+ $tests = New-Object System.Collections.ArrayList
+ $others = New-Object System.Collections.ArrayList
+ foreach ($row in $all) {
+ if ([string]$row[0] -like 'DB[_]%') { [void]$tests.Add($row) } else { [void]$others.Add($row) }
}
- Write-Host (" 测试库（DB_ 开头，取数程序已在读）：" + $testDatabases.Count + " 个")
- Write-Host (" 其他库（取数程序从未遍历）：" + $otherDatabases.Count + " 个")
- foreach ($row in $otherDatabases) { Write-Row $row }
- # ---------------------------------------------------------------- 2. 其他库里的表
- Write-Host ''
- Write-Host '=== 2. 其他库里的表 ==='
- $candidates = New-Object System.Collections.ArrayList
- foreach ($row in $otherDatabases) {
- $database = [string]$row[0]
- if (-not (Test-SafeName $database)) { Write-Host " 跳过库名异常的库：$database"; continue }
- Write-Host " [$database]"
- try {
- $tables = Invoke-ReadOnlyQuery $connection ("select name from [$database].sys.tables order by name")
+ if ($Database) {
+ $targets = New-Object System.Collections.ArrayList
+ [void]$targets.Add($Database)
+ Say "=== 1. 指定库探查：[$Database] ==="
+ }
+ else {
+ Say "=== 1. 探查范围 ==="
+ Say (" 用户库共 " + $all.Count + " 个；其中 DB_ 开头的测试库（取数程序已在读）" + $tests.Count +
+ " 个，本次不看")
+ Say (" 其他库 " + $others.Count + " 个，按创建时间倒序探查前 " + $MaxDatabases + " 个")
+ $targets = New-Object System.Collections.ArrayList
+ $index = 0
+ foreach ($row in $others) {
+ if ($index -ge $MaxDatabases) { break }
+ [void]$targets.Add([string]$row[0])
+ $index++
}
- catch { Write-Host (' 读取失败：' + $_.Exception.Message); continue }
- if ($tables.Count -eq 0) { Write-Host ' （没有表）'; continue }
- foreach ($table in $tables) {
- $tableName = [string]$table[0]
- Write-Host " $tableName"
- # 方案/配方/任务这类表最可能带工步名称，登记下来看列与样例行。
- if ($tableName -match '(?i)(step|recipe|task|plan|config|program|scheme|procedure|formula)') {
- [void]$candidates.Add([pscustomobject]@{ Database = $database; Table = $tableName })
- }
+ if ($others.Count -gt 0 -and $others.Count -le 20) {
+ Say (" 其他库清单：" + (($others | ForEach-Object { [string]$_[0] }) -join '、'))
}
}
+ if ($targets.Count -eq 0) {
+ Say ''
+ Say '=== 结论：库里没有其他库，工步方案不在数据库中 ==='
+ Say ' 下一步走文件侧：软件目录里的工步方案文件（.sto 方案、TaskModel.xml 等）。'
+ }
+
+ # ---------------------------------------------------------------- 2. 找候选表
+ $candidates = New-Object System.Collections.ArrayList
+ $tableCount = 0
+ foreach ($database in $targets) {
+ if (-not (Test-SafeName $database)) { Say " 跳过库名异常的库：$database"; continue }
+ try {
+ $byName = Invoke-ReadOnlyQuery $connection (
+ "select name from [$database].sys.tables where $planishTable")
+ $byColumn = Invoke-ReadOnlyQuery $connection @"
+select tb.name
+from [$database].sys.tables tb
+where exists (select 1 from [$database].sys.columns c
+ where c.object_id = tb.object_id and $stepishColumn)
+ and exists (select 1 from [$database].sys.columns c
+ where c.object_id = tb.object_id and $nameishColumn)
+"@
+ $tableCount += (Invoke-ReadOnlyQuery $connection "select count(*) from [$database].sys.tables")[0][0]
+ }
+ catch { Say (" 读取 [$database] 失败：" + $_.Exception.Message); continue }
+ $seen = @{}
+ foreach ($row in $byName) {
+ $table = [string]$row[0]
+ if ($seen.ContainsKey($table)) { continue }
+ $seen[$table] = $true
+ [void]$candidates.Add([pscustomobject]@{ Database = $database; Table = $table; Why = '表名像方案' })
+ }
+ foreach ($row in $byColumn) {
+ $table = [string]$row[0]
+ if ($seen.ContainsKey($table)) { continue }
+ $seen[$table] = $true
+ [void]$candidates.Add([pscustomobject]@{ Database = $database; Table = $table; Why = '含工步列+名称列' })
+ }
+ }
+
+ Say ''
+ Say '=== 2. 候选表统计 ==='
+ Say (" 探查 " + $targets.Count + " 个库、共 " + $tableCount + " 张表；命中候选 " + $candidates.Count + " 张")
# ---------------------------------------------------------------- 3. 候选表的列与样例行
- Write-Host ''
- Write-Host '=== 3. 候选表（表名含 step/recipe/task/plan/config 等）的列与样例行 ==='
- if ($candidates.Count -eq 0) {
- Write-Host ' 没有匹配到候选表。'
- }
+ $hits = 0
foreach ($candidate in $candidates) {
$database = $candidate.Database
$table = $candidate.Table
if (-not (Test-SafeName $table)) { continue }
- Write-Host " [$database].[$table]"
try {
+ # 带上 schema：表不一定在 dbo 下，用真实 schema 拼三段名，否则计数与取样会失败。
$columns = Invoke-ReadOnlyQuery $connection @"
-select c.name, ty.name as type_name
+select c.name, schema_name(tb.schema_id) as schema_name
from [$database].sys.columns c
join [$database].sys.tables tb on tb.object_id = c.object_id
-join [$database].sys.types ty on ty.user_type_id = c.user_type_id
where tb.name = '$table'
order by c.column_id
"@
+ $schema = [string]$columns[0][1]
+ if (-not (Test-SafeName $schema)) { continue }
+ $rows = (Invoke-ReadOnlyQuery $connection "select count(*) from [$database].[$schema].[$table]")[0][0]
}
- catch { Write-Host (' 读取列失败：' + $_.Exception.Message); continue }
+ catch { continue }
$names = @()
+ $allColumns = @()
foreach ($column in $columns) {
$columnName = [string]$column[0]
- Write-Host (' ' + $columnName + ' : ' + [string]$column[1])
- if ($columnName -match '(?i)(name|desc|type|no|id|index|idx)') { $names += $columnName }
+ $allColumns += $columnName
+ if ($columnName -match $nameishLike) { $names += $columnName }
}
+ # 只报"有名称类列"的表：没有名称列的表存不下工步名称，不是我们要找的。
if ($names.Count -eq 0) { continue }
- # 只挑"像是名称/编号"的列看几行：这是为了判断这张表里到底有没有工步名称，不导出业务数据。
- $projection = ($names | Select-Object -First 8 | ForEach-Object { "[$_]" }) -join ', '
+ if ($rows -le 0) { continue }
+ $hits++
+ # 命中太多就没法复制了：只打印前 -MaxHits 条，其余只计数（报告文件里也只有这些）。
+ if ($hits -gt $MaxHits) { continue }
+ Say ''
+ Say (" [$database].[$schema].[$table] 行数 $rows 命中原因：" + $candidate.Why)
+ Say (" 列：" + ($allColumns -join ', '))
+ $projection = ($names | Select-Object -First 6 | ForEach-Object { "[$_]" }) -join ', '
try {
- $sample = Invoke-ReadOnlyQuery $connection "select top $SampleRows $projection from [$database].dbo.[$table]"
- foreach ($line in $sample) { Write-Row $line }
+ $sample = Invoke-ReadOnlyQuery $connection "select top $SampleRows $projection from [$database].[$schema].[$table]"
+ foreach ($line in $sample) {
+ Say (' 样例：' + (($line | ForEach-Object { if ($null -eq $_) { '<null>' } else { [string]$_ } }) -join ' | '))
+ }
}
- catch { Write-Host (' 取样失败：' + $_.Exception.Message) }
+ catch { Say (' 取样失败：' + $_.Exception.Message) }
}
- Write-Host ''
- Write-Host '探查结束：本条只读，未写入任何数据。'
+ Say ''
+ if ($hits -eq 0) {
+ Say '=== 结论：没有找到"带工步名称的表" ==='
+ Say ' 下一步：① 提高 -MaxDatabases 再看一遍；② 转文件侧，查软件目录里的工步方案文件。'
+ }
+ else {
+ Say ("=== 结论：命中 " + $hits + " 张带名称列的候选表（最多打印 " + $MaxHits + " 张，见上），把上面这段发回即可 ===")
+ }
+ Say ('报告同时写在：' + $ReportFile)
}
-finally { $connection.Close(); $connection.Dispose() }
+finally {
+ $connection.Close(); $connection.Dispose()
+ # 中途出错也把已有内容落盘：控制台刷不完，文件可以慢慢复制。
+ $report | Set-Content -LiteralPath $ReportFile -Encoding UTF8
+}
--
Gitblit v1.7.1