SQLServerPowerShellでテーブル名とカラム名取得!

▼ “C:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLPS.exe”
(σ゚∀゚)σゲッツ!!

#▼ テーブルの場所まで移動
Push-Location SQLSERVER:\SQL\サーバー名\インスタンス名\Databases\データベース名\Tables

#▼ テーブル名とカラム名取得
Get-ChildItem | ForEach-Object { "▼ {0}`n{1}" -f $_.Name, [String]$_.Columns }

#▼ 元の場所に戻る
Pop-Location

▼ msdn
SQL Server PowerShell の実行

[PowerShell] : SQL Server 2008 でバックアップを取る方法(Agent版あり)

実は、SQL Server 2008 のバックアップもPowerShellで簡単に実行することができます。

こんな、バックアップスクリプトをSQLで作っておき、それをInvoke-SqlCmd で、ファイル名を指定して実行するだけ。

SQLスクリプト

DECLARE @backupSetId as int 

DECLARE @now as nvarchar(10)
DECLARE @backupfile nvarchar(max)
SELECT 	@now = CONVERT(varchar, getdate(), 112),
@backupfile = N'D:\backup\SampleDb_' + @now + '.bak'
BACKUP DATABASE [SampleDb] TO  DISK = @backupfile
WITH NOFORMAT,
INIT,
NAME = N'SampleDb-完全 データベース バックアップ',
SKIP,
NOREWIND,
NOUNLOAD,
STATS = 10
SELECT 	@backupSetId = position
FROM
msdb..backupset
WHERE
database_name = N'SampleDb'
AND 	backup_set_id = (select max(backup_set_id) from msdb..backupset where database_name=N'SampleDb' )
IF @backupSetId is null
BEGIN
raiserror(N'確認に失敗しました。データベース ''SampleDb'' のバックアップ情報が見つかりません。', 16, 1)
END
RESTORE VERIFYONLY FROM  DISK = @backupfile WITH  FILE = @backupSetId, NOUNLOAD,  NOREWIND

上記バックアップスクリプト実行するPowerShellのコマンドライン

 # Invoke-SqlCmd -InputFile 'バックアップ用SQLスクリプト'
Invoke-SqlCmd -InputFile 'D:\backup\SampleDb_Backup.sql'

たった、これだけなんですが、1つだけ注意点!

それは、Invoke-SqlCmd コマンドをSQL Server Agent で動かす時、このままだとエラーになります。

ちなみに、SQL Server Agent で、PowerShellを動かしたい場合は、下記で行けます。

手順1:Management Studio で、[管理]→[SQL Server Agent] を選択する

手順2:新規ジョブを作成し、下記のように設定する

手順3:ジョブを作成する際、[種類]にPowerShellを選択し、コマンド欄には、下記のように入力します。(SQL Server 認証の例)

  
# ユーザIDとパスワードを指定する
#(コマンドライン実行と違い、SQL Server Agentは接続情報をもっていないため)
Invoke-SqlCmd -InputFile 'D:\backup\SampleDb_Backup.sql'
-ServerInstance "サーバ名\SQL Serverのインスタンス名" -U ユーザID -P パスワード

手順4:後は、ジョブに対するエラー発生時の対処方法などを設定すればOKです。

手順5:作成したジョブを右クリックし、[ステップでジョブを開始] します。

成功メッセージが返ってきて、所定のフォルダにDBバックアップファイルが無事格納されていればOKです。

ただ、もう1つわからないことがあります。(T-SQL を私が忘れているだけかもしれませんが)

それは、Invoke-SqlCmd コマンドって、SQL Server でいう、EXEC(正式名はEXECUTE) ですよね?

ストアド実行時に関しては戻り値がとれるんで、OKなんですが、SQLスクリプトファイルで実行したものって

戻り値とれないんでしたっけ?

もし、それができれば、Invoke-SqlCmd でスクリプトファイルをSQL Server Agent で実行しても、

きちんとした戻り値処理ができるんですけどね・・・ うーん、知っている方、教えてください。

続編:SQL Server 2008 に定義されているDB内のすべてのビューと列情報をPowerShellで取得する方法

ネタ元:SQL Server 2008 に定義されているテーブルやビューの列情報をPowerShellで取得する方法

前エントリを応用し、DB内にあるすべてのビューの列情報をCSVに書き出すようにしてみました。

データ型によっては出さないほうが良い列情報もあるのですが、

そこはWhere-Objectコマンドレットで制御可能なので、今回はあまり気にしないで作ってみました。

また、前エントリにて、

「Format-Tableではなく、System.String.Format に相当するものを -f で指定できる」

というのを教えて頂きましたので、それらを踏まえて、下記のようにしてみました。

定義済みのView

# DB内にあるすべてのViewを取得
#$views = (Get-ChildItem SQLSERVER:\SQL\サーバー名\インスタンス名(通常はDefaultなはず)\Databases\対象DB名\Views)
$views = (Get-ChildItem SQLSERVER:\SQL\37-47\MSSQLSERVER_2008+sa\Databases\SampleDb\Views)
$outFileName = "c:\work\ViewDocument.csv"

# Viewごとのループ
foreach( $view in $views )
{
    "$view`r`n" | Out-File $outFileName -Append

    # View名を取得
    # ( SQL Server がPowerShell変数を識別するために、
    #   "SQL Server変数=PowerShell変数" という書式で定義する。ホスト変数の定義方法の1つです)
    $viewname = "VIEWNAME=" + $view.Name

    # Viewの詳細をCSVファイルに出力する
    Invoke-Sqlcmd -Database "SampleDB" -Query "sp_help N'[dbo].[`$(VIEWNAME)]'" -variable $viewname |
    Where-Object {$_.Column_name -ne $null} |
    % {"{0}`t{1}({2})`t({3},{4})" -f $_.Column_name, $_.Type, $_.Length, |
    [System.Convert]::ToString($_.Prec).Trim(), [System.Convert]::ToString($_.Scale).Trim() } |
    Out-File $outFileName -Append

    # 次のViewへ
    "`r`n" | Out-File $outFileName -Append
}

★実行結果

だいぶ、テーブル/ビュー定義書に必要な情報がPowerShellでそろえられるようになってきました。

今回はビューの例でやりましたが、テーブルでも同じようにできるので、参考にしてみてください。

膨大な数のテーブル/ビューの定義書を作る場合、こういうのがあると作業を効率化できますし、

また、ビューの場合、テーブルと違って、SQL Server のデザイン画面で、

各項目の型を閲覧することができませんので、今回のようなツールがあると、かなり助かると思います。

SQL Server 2008 に定義されているテーブルやビューの列情報をPowerShellで取得する方法

SQL Server 2008内にあるテーブルとビューの列情報をPowerShellで取得する方法をやってみました。

SQL Server の sp_help コマンドを、PowerShellのInvoke-SqlCmd コマンドレットで呼び出しています。

★テーブルやビューの列情報をPowerShellで取得する際の書式

Invoke-Sqlcmd -Database "データベース名" -Query "sp_help N'[対象スキーマ名].[対象テーブル名またはビュー名]'" |
Format-Table [表示したい項目1], [表示したい項目2]......................... -autosize

SQL Server で通常、sp_help ストアドをキックすると、下記図のように複数の結果セットが表示されます。

そのため、PowerShellで、sp_help の結果を取得するには、Format-Tableで表示する項目を指定して

あげるなど、工夫が必要です。

★サンプルに使用したテーブル

CREATE TABLE [dbo].[SampleTable](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [column1] [nvarchar](max) NULL,
    [column2] [datetime] NULL,
    [column3] [numeric](18, 0) NULL,
    [column4] [decimal](18, 0) NULL,
CONSTRAINT [PK_SampleTable] PRIMARY KEY CLUSTERED (
    [id] ASC)WITH (
    PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF,
    IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON,
    ALLOW_PAGE_LOCKS  = ON)
ON [PRIMARY])
ON [PRIMARY]

★サンプルに使用したビュー

CREATE VIEW [dbo].[SampleView]AS
SELECT
    id,
    column1,
    column2,
    column3,
    column4
FROM
  dbo.SampleTable

★テーブルの列情報をPowerShellで取得する使用例

Invoke-Sqlcmd -Database "SampleDB" -Query "sp_help N'[dbo].[SampleTable]'" |
Format-Table Column_name, Type -autosize

■実行結果

★ビューの列情報をPowerShellで取得する使用例

Invoke-Sqlcmd -Database "SampleDB" -Query "sp_help N'[dbo].[SampleView]'" |
Format-Table Column_name, Type -autosize

■実行結果

SQL Server の sp_helpが使用できるものであれば、テーブルだろうとビューだろうと、ストアドやファンクションであっても何でも取得できます。

★さらに上記の応用で、テーブル設計書を作りたい人は、下記のようにテーブルやビューの情報をファイル出力させることもできます。

Invoke-Sqlcmd -Database "SampleDB" -Query "sp_help N'[dbo].[SampleView]'" |
Format-Table Column_name, Type -autosize |
Out-File c:\work\SampleView.txt

■実行結果

ただ、Export-csv コマンドレットもしくはOut-Fileコマンドレットでも良いので、

綺麗に体裁を整えた設計書に近い、ファイル出力方法がないか現在模索中・・・(仕事で入り用になったため)

(いろいろと模索しはじめると、私みたいなPowerShell初心者は、頭がパニックになります orz)

少し頭を整理しないと、ダメですね・・・