我有個快速備份 SQL 資料庫 View、Function 以及 Stored Procedure 定義的需求,顯然球又飛進 PowerShell 的守備範圍。

SQL Server 有個 sys.sql_modules 資料表,包含 View、Function、Stored Procedure、Trigger、Default... 等物件定義內容,其中 definition nvarchar(max) 欄位存有定義該物件的 SQL 指令。要查詢物件名稱、類別、是否為系統物件... 等資訊則需 JOIN sys.objects。(註:在網路爬文會看到性質相似的 sysobjects、syscomments 資料表,至今雖然仍可使用,但他們是基於向前相容保留的,未來會被移除,故建議改用新版資料表。新舊系統資料表對映表在這裡)

為了測試,在 SQL LocalDB 隨便建了一個 View 一個 Function 及一個 Procedure:

核心查詢實測如下,透過 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
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

透過以上述查詢,CREATE 指令輕鬆到手:

接下來,寫一小段 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()

測試成功。

Example of using PowerShell to export views, functions and procedures as CREATE script files from MSSQL server.


Comments

# by Tim

您好,這篇非常有用!不過撈出來的SP/FN/Views 好像少了換行符號?請問有什麼特別可以處理的方式嗎?

# by Jeffrey

to Tim, 你是用 SSMS 查詢嗎? 它顯示欄位資料時會拿掉換行,用範例的 PowerShell 透過程式讀取,可以得到包含換行的結果。

# by Tim

Jeffrey 您好,是的,是用SSMS查詢,我用程式跑看看好了,感謝!

Post a comment


77 - 35 =