#▼ コンマ区切り

"SELECT * FROM tChiaki" | osql -S CHIAKIPC\SQLEXPRESS -E -d chiakiDB "-s,"



#▼ ↑の他区切り

"SELECT * FROM tChiaki" | osql -S CHIAKIPC\SQLEXPRESS -E -d chiakiDB -s♪

▼ SQLPS.exe
"C:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLPS.exe"

▼ C:\Users\chiaki\PSandSQL.ps1

#&"$home\PSandSQL.ps1"



$strSQL = @"

CREATE TABLE tChiaki (f1 int primary key not null, f2 varchar(50))

INSERT INTO tChiaki VALUES (1, 'PowerShell が大好きです！')

INSERT INTO tChiaki VALUES (2, 'SELECT * FROM が大好きです！')

INSERT INTO tChiaki VALUES (3, '1と2でご飯３杯いけます！！')

SELECT * FROM tChiaki

DROP TABLE tChiaki

"@



Invoke-Sqlcmd -ServerInstance "CHIAKIPC\SQLEXPRESS" -Database "chiakiDB" -Query $strSQL | Format-Table -AutoSize

▼ Bing検索結果
つ｜invoke-sqlcmd - Bing｜
つ｜format-table powershell - Bing｜

実は、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'

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

ネタ元：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変数"　という書式で定義する。ホスト変数の定義方法の１つです）    
    $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 の 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）

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