產生資料庫物件的T-SQL指令碼

本文將介紹各種產生資料庫物件的T-SQL指令碼的方法。

情境描述

若您想要把已經建立在SQL Server中的資料庫物件轉換為T-SQL指令碼,以方便進行版控或是拿到其他環境中去使用,針對這個需求,筆者稍微整理一下幾個可以用來達成這個目的的做法,請參考下一節。

(印象中之前好像也有寫過類似的文章,但忘記是歸到哪一類了,索性再整理一次)

針對特定資料庫物件

以資料表物件為例,若您在物件總管中特定資料表上按滑鼠右鍵,選擇【編寫資料表的指令碼為>CREATE至>剪貼簿】是可以透過SSMS來產生單一資料表的T-SQL指令碼,但用這個方法的缺點是,一次只能一個資料表,若您有大量資料表或資料庫物件要產生指令碼,使用這種方式便顯得比較沒效率。

image

比較快速的方式是在功能表中點選【檢視>物件總管詳細資料】或是直接在SSMS中按快速鍵F7,來開啟物件總管詳細資料視窗。

image

接著點選物件總管中您所要產生指令碼的物件,筆者一樣是以資料表為例,當您在物件總管中點選資料表時,右側的物件總管詳細資料就會呈現出所有資料表物件,在這個視窗中您就可以搭配CTRL或SHIFT來複選資料表物件,進而編寫資料表的指令碼。

image

針對整個資料庫物件

若您想要針對整個資料庫產生T-SQL指令碼,那麼產生和發佈指令碼精靈會是比較方便的選擇,整個過程只需要四個步驟就可以輕鬆完成。

請在物件總管中於您要產生指令碼的資料庫節點上按滑鼠右鍵,選擇【工作>產生指令碼】。

image

SSMS會幫您呼叫產生和發佈指令碼精靈,請在歡迎畫面中按下一步。

image

預設會將整個資料庫物件中的資料庫物件產生為指令碼,您可以依照您的需求來選擇只要編寫資料表、檢視或預存程序的指令碼。

image

輸出的部分可以讓您選擇要將指令碼產生到檔案、剪貼簿或查詢視窗,您也可以將指令碼直接發佈到Web服務。

image

利用產生和發佈指令碼精靈的另外的好處是,您可以按下進階來設定編寫指令碼的細部選項,其中值得一提的是您可以順便在【要編寫指令碼的資料類型】選項中選擇僅限結構描述、僅限資料或是資料結構和資料,讓您依照需求自行決定是否要連資料一同幫您產生為指令碼。另外,針對【伺服器版本編寫指令碼】選項可以用來幫助您做資料庫降級,若您要部屬指令碼的SQL Server執行個體版本比目前產生指令碼的還舊,可以在這個選項選擇適當的伺服器版本,會讓整個部署的過程更加順利。

image

接著是檢閱您所選取的項目,若沒問題請按下一步。

image

接著產生和發佈指令碼就會幫您將所選擇的資料庫物件編寫為指令碼。

image

使用資料層應用程式

這個解法放在這篇文章似乎比較不恰當,因為資料層應用程式(DAC)不會幫您產生資料庫物件的T-SQL指令碼,您可以利用匯出資料層應用程式的功能來將資料庫物件打包成DACPAC封裝檔案。

image

同樣只要四個步驟,請在歡迎畫面上直接按下一步。

image

接著設定DAC屬性,您可以在此指定資料層應用程式的名稱,預設是以您擷取資料層應用程式的資料庫名稱作為應用程式名稱,既然是應用程式因此版本也是相當重要,可以用來識別您DAC的版次,最後則是指定DAC封裝檔案的儲存路徑,若封裝檔案已經存在,請勾選覆寫現有檔案才可以繼續下一步。

image

再來的步驟是摘要資訊,您可以在此確認您DAC的屬性是否正確,若沒問題按下一步繼續。

image

最後擷取資料層應用程式精靈就會開始幫您建立DAC封裝檔案。

image

DAC封裝檔案產生之後,您就可以從物件總管中點選【資料庫>部署資料層應用程式】來透過DAC封裝檔案部署資料庫。

image

歡迎畫面一樣直接按下一步。

image

然後就是選取您從其他SQL Server擷取的DAC封裝檔案的位置。

image

若您要部署的資料庫已經存在請指定不同的資料庫名稱。

image

在摘要資訊的步驟中檢視您的設定是否正確,若沒問題請按下一步。

image

接下來部署資料層應用程式精靈就會開始幫您把DAC封裝檔案拆解並部署成資料庫。

image

使用SQL Server資料庫專案

若您想要將資料庫物件納入版控甚至部署,您可以使用SQL Server資料庫專案,請先開啟SSDT,接著在起始頁點選【新增專案>SQL Server>SQL Server資料庫專案】,輸入專案名稱、位置及方案名稱等資訊後按確定。

image

專案建立成功後請在方案總管視窗,您的資料庫專案節點按滑鼠右鍵選擇【匯入>資料庫】。

image

然後在匯入資料庫視窗中指定您要匯入資料庫物件的資料庫連線字串,設定完畢後按啟動。

image

此時就會開始蒐集資料庫物件相關資訊並開始匯入到資料庫專案。

image

image

匯入成功後您將在方案總管中看到所有資料庫物件已經被產生成相對應的.sql檔案,您可以用來把資料庫物件的檔案納入版控,並且透過SSDT來部署資料庫專案。

image

參考資料

產生和發佈指令碼精靈

SQL Server Hosting Web Service (and toolkit)