核心查詢實測如下,透過 type in ('V','FN','IF','P') 限定 View/Function/Procedure,is_ms_shipped = 0 則用以排除 SQL 內建物件。
select m.object_id,m.definition,o.name,o.type, o.type_desc, o.modify_date
from sys.sql_modules m join sys.objects o
on m.object_id = o.object_id
where o.type in ('V','FN','IF','P')
and o.is_ms_shipped = 0
order by o.name,o.type
接下來,寫一小段 PowerShell 執行查詢,讀取 View、Function、Procedure 的 CREATE Script,以物件名稱作為檔案名,分別儲存到 Views、Funcs、SPs 子資料就大功告成。
$ErrorActionPreference = "STOP"
# 提醒:範例為求精簡明碼寫死連線字串,實務應用時應加密並另行保存
$cs = "Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=path-to-mdf;Integrated Security=True"
$cn = [System.Data.SqlClient.SqlConnection]::new($cs)
$cn.Open()
$cmd = $cn.CreateCommand()
$cmd.CommandText = @"
select m.object_id,m.definition,o.name,o.type, o.type_desc
from sys.sql_modules m join sys.objects o
on m.object_id = o.object_id
where o.type in ('V','FN','IF','P')
and o.is_ms_shipped = 0
"@
$dr = $cmd.ExecuteReader()
$workPath = Get-Location
while ($dr.Read()) {
$type = $dr["type"].ToString().Trim()
$subFolder = "Views"
if ($type -eq 'P') { $subFolder = "SPs" }
elseif ($type -eq 'FN' -or $type -eq 'IF') { $subFolder = 'Funcs' }
$name = $dr["name"].ToString()
$targetFolder = [System.IO.Path]::Combine($workPath, $subFolder)
[System.IO.Directory]::CreateDirectory($targetFolder) | Out-Null
$dr["definition"].ToString() |
Out-File ([System.IO.Path]::Combine($targetFolder, $name + ".sql")) -Encoding utf8
Write-Progress -Activity "匯出資料庫檢視、函式與 SP" -Status $name
}
$cn.Dispose()