<# ======================================================================
  Windows PowerShell 5.1
  ★ 単一接続で複数DBを横断検索（[db].sys.sql_modules）
  ・対象: SP(P) / View(V)   ※必要なら type を拡張可能
  ・判定: LIKE（単純）
  ・出力: db / schema / type / source を 1つのTSV（UTF-8 BOM, 切れなし）
====================================================================== #>

# ===== 設定（ここだけ編集） ===========================================
# 単一の SQL Server インスタンス
$ServerInstance = "localhost\SQLEXPRESS"

# 横断対象のデータベース（1接続で参照）
$Databases = @(
    "YourDb1",
    "YourDb2"
    # ここに追加...
)

# LIKE のパターン（% を含める）
$Pattern = "%検索したい文字列%"

# 出力ファイル（1つに集約）
$OutputFile = ".\SqlTextSearch_{0:yyyyMMdd_HHmmss}.tsv" -f (Get-Date)
# =====================================================================

Add-Type -AssemblyName "System.Data"

# 出力先フォルダを準備
$dir = Split-Path -Parent $OutputFile
if (![string]::IsNullOrEmpty($dir) -and -not (Test-Path $dir)) {
    New-Item -ItemType Directory -Path $dir | Out-Null
}

# TSV ヘッダ（BOM付き UTF-8）
"db`tschema`ttype`tsource" | Out-File -FilePath $OutputFile -Encoding UTF8

# --- 三部構文で横断するための動的T-SQLを1本に構築 ---
#   例:
#   SELECT N'DbA', s.name, o.type_desc, m.definition FROM [DbA].sys.sql_modules ...
#   UNION ALL
#   SELECT N'DbB', s.name, o.type_desc, m.definition FROM [DbB].sys.sql_modules ...
# -------------------------------------------------------
$sqlParts = New-Object System.Collections.Generic.List[string]
foreach ($db in $Databases) {
    # DB名中の ']' をエスケープ（[Db]]Name]）
    $dbBracketed = "[" + ($db -replace ']', ']]') + "]"

    $sqlParts.Add(@"
SELECT
  N'$db'            AS db,
  s.name            AS schema_name,
  o.type_desc       AS object_type,
  m.definition      AS source_text
FROM $dbBracketed.sys.sql_modules AS m
JOIN $dbBracketed.sys.objects     AS o ON m.object_id = o.object_id
JOIN $dbBracketed.sys.schemas     AS s ON o.schema_id = s.schema_id
WHERE o.is_ms_shipped = 0
  AND o.type IN ('P','V')       -- P: Stored Procedure, V: View
  AND m.definition LIKE @pattern
"@)
}
# 1本の SQL に UNION ALL で連結し、最後に並び替え
$finalSql = ($sqlParts -join "`r`nUNION ALL`r`n") + "`r`nORDER BY object_type, schema_name, db;"

# ---- 単一接続で実行（Initial Catalog は master など1つでOK） ----
$csb = New-Object System.Data.SqlClient.SqlConnectionStringBuilder
$csb["Data Source"]            = $ServerInstance
$csb["Initial Catalog"]        = "master"
$csb["Integrated Security"]    = $true
$csb["TrustServerCertificate"] = $true

$cn  = New-Object System.Data.SqlClient.SqlConnection $csb.ConnectionString
$cmd = $cn.CreateCommand()
$cmd.CommandText    = $finalSql
$cmd.CommandTimeout = 300

# LIKE パラメータ（@pattern）
$p = $cmd.Parameters.Add("@pattern",[System.Data.SqlDbType]::NVarChar,4000)
$p.Value = $Pattern

try {
    $cn.Open()
    $r = $cmd.ExecuteReader()

    $rowCount = 0
    while ($r.Read()) {
        $dbName = $r.GetString(0)
        $schema = $r.GetString(1)
        $type   = $r.GetString(2)
        $source = if ($r.IsDBNull(3)) { "" } else { $r.GetString(3) }

        # 1行追記（BOM付きUTF-8、Format-Table等を使わない＝切れない）
        ("{0}`t{1}`t{2}`t{3}" -f $dbName, $schema, $type, $source) |
            Out-File -FilePath $OutputFile -Encoding UTF8 -Append

        $rowCount++
    }
    $r.Close()
    Write-Host ("OK: {0} 行を書き出しました -> {1}" -f $rowCount, (Resolve-Path $OutputFile))
}
catch {
    Write-Warning ("失敗: {0}" -f $_.Exception.Message)
}
finally {
    if ($r)   { $r.Dispose() }
    if ($cmd) { $cmd.Dispose() }
    if ($cn.State -ne [System.Data.ConnectionState]::Closed) { $cn.Close() }
    $cn.Dispose()
}