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)

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

SQL Server 2008 に定義されているテーブルやビューの列情報をPowerShellで取得する方法” への6件のコメント

  1. その場合なら多分式の最後 Format-Table の代わりに -f 演算子を使った書式指定出力を行えばいいと思います
    Invoke-Sqlcmd -Database "SampleDB" -Query "sp_help N'[dbo].[SampleView]’" |
    % { ‘{0,-30}, {1,-30}’ -f $_.Column_name, $_.Type }
    等とやってあげれば見た目同じで CSV 形式にできると思いますよ( -f 演算子自体は .NET の System.String.Format メソッドの糖衣構文です)

    他には V2CTP2 の頃から ConvertTo-XML なるコマンドレットもあったり。。。

  2. > { ‘{0,-30}, {1,-30}’ -f $_.Column_name, $_.Type }

    おお!-f でもできますね。すばらしい!

    実は、今私が作っている仕様書Generatorは、上記サンプルでファイル出力させ、ファイルを読み直すよう作っていました。
    で、その読み直した時に、[System.String]::Format を使って書式変更し、さらにファイルを再出力させるようにしてました。
    今考えると、かなり馬鹿なことやってました・・・

    教えて頂きありがとうございます。(知らないというのは、ある意味、恐ろしいです)

    >ConvertTo-XML

    実は、ConvertTo-XML をものすごく使いたかったのですが、
    SQL Server 2008がインストール済みだと、V2CTP版をインストールできないことがわかり、
    断念した経緯があります。(SQL Server 2008 には、V1がインストールされるのですが、V1だけをアンインストールするオプションがSQL Server 2008にはないため)
    また、SQL Server 2008をアンインストールできない状況だったので、Convert-XMLを使えなかったことをかなり悔やんでいる最中でした・・・

  3. >けろさん
    良かったですね。
    また新たなネタの投稿よろしくお願いいたします。

    >S.K.さん
    いつもコメントありがとうございます。
    S.K.さんはPowerShellに関してかなりの知識をお持ちですね。

    もしよろしかったらPowerShell from Japan!!のメンバーになって一緒に情報を発信してみませんか?
    (けろさん、どさくさにまぎれての宣伝すみません)

    興味がありましたらコンタクトフォーム( http://powershell.hiros-dot.net/contact.aspx )よりご連絡ください。

    よろしくお願いいたします。

  4. 今更ですが出力されるテキスト自体の見栄えを気にしないなら
    > | select Column_name, Type | Export-Csv data.csv
    の方が良かったですかね・・・。

    傍目八目というか、PowerShellインアクション見返しただけですよ^^;<HIROさん

  5. 実は、Export-Csv は、Invoke-Sqlcmd と合わせて使うことができないバグがあるため、
    使用することができないんです。
    なので、修正パッチが出ない限り、
    >| select Column_name, Type | Export-Csv data.csv
    は使えません。(残念ですが・・・)

  6. そんな不具合があったんですね
    先に -f 演算子の方を思い出してよかったです;

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

次のHTML タグと属性が使えます: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>