8

SQL Server View、Function 及 Stored Procedure 定義之快速備份

 3 years ago
source link: https://blog.darkthread.net/blog/dump-sql-things/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
neoserver,ios ssh client
SQL Server View、Function 及 Stored Procedure 定義之快速備份-黑暗執行緒

我有個快速備份 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','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','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','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') { $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()

測試成功。


report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK