<#
|
22 所上位机 SQL Server 只读探查：找出「工步方案」在哪里（工步名称可能只存在于方案里）。
|
v2：那台实例有几千个库，全量罗列没法看，所以本版**只报结论**——不列库、不列表，
|
只找"像方案的表"与"同时含工步列和名称列的表"，命中项才打印细节；其余只给计数。
|
|
只读纪律与取数程序一致：只执行 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,
|
# 非 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，读满即断，不发加锁读、不开启事务。
|
function Invoke-ReadOnlyQuery($connection, [string]$sql) {
|
$command = $connection.CreateCommand()
|
$command.CommandText = $sql
|
$command.CommandTimeout = $CommandTimeoutSec
|
$reader = $command.ExecuteReader()
|
try {
|
$rows = New-Object System.Collections.ArrayList
|
while ($reader.Read()) {
|
$values = New-Object System.Collections.ArrayList
|
for ($i = 0; $i -lt $reader.FieldCount; $i++) {
|
if ($reader.IsDBNull($i)) { [void]$values.Add($null) }
|
else { [void]$values.Add($reader.GetValue($i)) }
|
}
|
[void]$rows.Add($values)
|
}
|
return , $rows
|
}
|
finally { $reader.Close(); $command.Dispose() }
|
}
|
|
# 表名像"方案/配方/任务"的，以及同时含工步列与名称列的，都算候选。
|
$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()
|
|
try {
|
Say "已连接：$SqlInstance（只读探查，不写任何数据）"
|
Say ''
|
|
# ---------------------------------------------------------------- 1. 要探查哪些库
|
$all = Invoke-ReadOnlyQuery $connection @'
|
select name, create_date
|
from sys.databases
|
where database_id > 4
|
order by create_date desc
|
'@
|
$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) }
|
}
|
|
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++
|
}
|
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. 候选表的列与样例行
|
$hits = 0
|
foreach ($candidate in $candidates) {
|
$database = $candidate.Database
|
$table = $candidate.Table
|
if (-not (Test-SafeName $table)) { continue }
|
try {
|
# 带上 schema：表不一定在 dbo 下，用真实 schema 拼三段名，否则计数与取样会失败。
|
$columns = Invoke-ReadOnlyQuery $connection @"
|
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
|
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 { continue }
|
$names = @()
|
$allColumns = @()
|
foreach ($column in $columns) {
|
$columnName = [string]$column[0]
|
$allColumns += $columnName
|
if ($columnName -match $nameishLike) { $names += $columnName }
|
}
|
# 只报"有名称类列"的表：没有名称列的表存不下工步名称，不是我们要找的。
|
if ($names.Count -eq 0) { continue }
|
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].[$schema].[$table]"
|
foreach ($line in $sample) {
|
Say (' 样例：' + (($line | ForEach-Object { if ($null -eq $_) { '<null>' } else { [string]$_ } }) -join ' | '))
|
}
|
}
|
catch { Say (' 取样失败：' + $_.Exception.Message) }
|
}
|
|
Say ''
|
if ($hits -eq 0) {
|
Say '=== 结论：没有找到"带工步名称的表" ==='
|
Say ' 下一步：① 提高 -MaxDatabases 再看一遍；② 转文件侧，查软件目录里的工步方案文件。'
|
}
|
else {
|
Say ("=== 结论：命中 " + $hits + " 张带名称列的候选表（最多打印 " + $MaxHits + " 张，见上），把上面这段发回即可 ===")
|
}
|
Say ('报告同时写在：' + $ReportFile)
|
}
|
finally {
|
$connection.Close(); $connection.Dispose()
|
# 中途出错也把已有内容落盘：控制台刷不完，文件可以慢慢复制。
|
$report | Set-Content -LiteralPath $ReportFile -Encoding UTF8
|
}
|
