隨記

Table / View 下載指南

這份文件說明如何依指定 schema 批次匯出該 schema 底下的 table 與 view 定義。

流程

  1. 先指定要匯出的 schemaName。
  2. 產生該 schema 下的 table / view 清單。
  3. 逐一 script 出物件定義。
  4. table 需包含 schema、PK/FK、default constraint、check constraint、indexes。
  5. 每個 table 使用一個資料夾保存，裡面拆成多個 .sql 檔。

1. 指定 schema

$schemaName = 'sellout2pr'
$outputFolder = 'D:\SPs'

2. 抓出 table / view 清單

$tables = ssql -env fcst_dev -Q "SET NOCOUNT ON; SELECT QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(name) FROM sys.tables WHERE SCHEMA_NAME(schema_id) = '$schemaName'" -h-1
$views  = ssql -env fcst_dev -Q "SET NOCOUNT ON; SELECT QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(name) FROM sys.views WHERE SCHEMA_NAME(schema_id) = '$schemaName'" -h-1

3. 存成清單

$tables | Set-Content -LiteralPath "$outputFolder\tables.txt"
$views  | Set-Content -LiteralPath "$outputFolder\views.txt"

4. 匯出 table / view

做法是：

  1. 用清單抓出指定 schema 的 table / view。
  2. 針對 view 直接輸出 sys.sql_modules.definition。
  3. 針對 table 用自訂 T-SQL 組出 CREATE TABLE，再把 PK/FK/default/check/index 拆成獨立檔案。

這種方式可行，但 table 的腳本會比較長。

View 匯出

$views = ssql -env fcst_dev -Q "SET NOCOUNT ON; SELECT QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(name) FROM sys.views WHERE SCHEMA_NAME(schema_id) = '$schemaName'" -h-1
$views | Set-Content -LiteralPath "$outputFolder\views.txt"

foreach ($view in Get-Content -LiteralPath "$outputFolder\views.txt") {
    $safeView = $view.Replace("'", "''")
    $fileName = ($view.Replace('[','').Replace(']','').Replace('.','_')) + '.sql'

    ssql -env fcst_dev -Q "SET NOCOUNT ON; SELECT m.definition FROM sys.sql_modules m JOIN sys.objects o ON m.object_id = o.object_id WHERE o.type = 'V' AND o.object_id = OBJECT_ID('$safeView')" -y 0 -o "$outputFolder\$fileName"
}

Table 匯出

$tables = ssql -env fcst_dev -Q "SET NOCOUNT ON; SELECT QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(name) FROM sys.tables WHERE SCHEMA_NAME(schema_id) = '$schemaName'" -h-1
$tables | Set-Content -LiteralPath "$outputFolder\tables.txt"

每個 table 建議建立一個 folder，例如：

D:\SPs\sellout2pr.TableName\
  create_table.sql
  pk.sql
  fk.sql
  default_constraints.sql
  check_constraints.sql
  indexes.sql

接著用一段自訂 T-SQL 組 CREATE TABLE，再另外查：

  • sys.key_constraints / sys.indexes 取得 PK，輸出到 pk.sql
  • sys.foreign_keys / sys.foreign_key_columns 取得 FK，輸出到 fk.sql
  • sys.default_constraints 取得 default constraint，輸出到 default_constraints.sql
  • sys.check_constraints 取得 check constraint，輸出到 check_constraints.sql
  • sys.indexes / sys.index_columns 取得 indexes，輸出到 indexes.sql

每一類細節建議各自產生一個腳本，方便單獨查看和重跑。

建議的輸出規則

$tableFolder = Join-Path $outputFolder ($table.Schema + '.' + $table.Name)
New-Item -ItemType Directory -Path $tableFolder -Force | Out-Null

然後分別輸出：

  • create_table.sql
  • pk.sql
  • fk.sql
  • default_constraints.sql
  • check_constraints.sql
  • indexes.sql

各檔案的產生方式

create_table.sql

用 sys.columns、sys.types、sys.identity_columns 組出欄位定義，再加上 NULL / NOT NULL / IDENTITY / COLLATE 等資訊。

ssql -env fcst_dev -Q "SET NOCOUNT ON; -- 在這裡輸出 CREATE TABLE 內容" -y 0 -o "$tableFolder\create_table.sql"

pk.sql

查 sys.key_constraints、sys.indexes、sys.index_columns，找出主鍵欄位與約束名稱後輸出。

ssql -env fcst_dev -Q "SET NOCOUNT ON; -- 在這裡輸出 PK 內容" -y 0 -o "$tableFolder\pk.sql"

fk.sql

查 sys.foreign_keys、sys.foreign_key_columns、sys.tables、sys.columns，輸出外鍵關聯。

ssql -env fcst_dev -Q "SET NOCOUNT ON; -- 在這裡輸出 FK 內容" -y 0 -o "$tableFolder\fk.sql"

default_constraints.sql

查 sys.default_constraints、sys.columns，輸出欄位預設值。

ssql -env fcst_dev -Q "SET NOCOUNT ON; -- 在這裡輸出 default constraint 內容" -y 0 -o "$tableFolder\default_constraints.sql"

check_constraints.sql

查 sys.check_constraints，輸出 check 條件。

ssql -env fcst_dev -Q "SET NOCOUNT ON; -- 在這裡輸出 check constraint 內容" -y 0 -o "$tableFolder\check_constraints.sql"

indexes.sql

查 sys.indexes、sys.index_columns、sys.columns，輸出非主鍵索引與其欄位排序。

ssql -env fcst_dev -Q "SET NOCOUNT ON; -- 在這裡輸出 indexes 內容" -y 0 -o "$tableFolder\indexes.sql"

5. 注意事項

  • -h-1 只適用於 ssql 查清單。
  • 如果你只想匯出基本結構，可以先只產 create_table.sql 和 view.sql。
  • table folder 建議命名成 schema.table。