SQLServer テーブル定義の取得

テーブル定義情報を取得してＣＳＶに出力する

テーブル定義 取得用 SQL

lesson002.sql

select

    c.table_name

,   c.ordinal_position

,   c.column_name

,   c.data_type

,   c.character_maximum_length

,   c.numeric_precision

,   c.numeric_precision_radix

,   c.numeric_scale

,   c.is_nullable

,   c.column_default

,   p.constraint_type

from

    information_schema.columns      c

left outer join

    (

        select

            k.table_catalog

        ,   k.table_schema

        ,   k.table_name

        ,   k.column_name

        ,   t.constraint_type

        from 

            information_schema.key_column_usage     k

        inner join 

            information_schema.table_constraints    t

        on 

            (   t.constraint_name   =   k.constraint_name   )

        where 

            (   t.constraint_type   =   'primary key'       )

    )

    p

on 

        (   c.table_catalog =   p.table_catalog     )

    and (   c.table_schema  =   p.table_schema      )

    and (   c.table_name    =   p.table_name        )

    and (   c.column_name   =   p.column_name       )

order by

    c.table_name

,   c.ordinal_position

接続文字列

lesson002.con

DRIVER={SQL Server};SERVER=xxxxxx;DataBase=adventureworks;UID=yyyyyy;PWD=zzzzzz;

ＣＳＶ出力用スクリプト

lesson002.vbs

Option Explicit

Private fs
Set fs = CreateObject("Scripting.FileSystemObject")

'接続文字列 読み込み
Private tsCon
Set tsCon = fs.OpenTextFile(WScript.Arguments(0))

Private sCon
sCon = ""
If Not tsCon.AtEndOfStream Then
    sCon = tsCon.ReadLine
End If
tsCon.Close
Set tsCon = Nothing

'接続
Private con
Set con = CreateObject("ADODB.Connection")
With con
    .Provider         = "SQLOLEDB"
    .ConnectionString = sCon
    .Open
End With

'SQL 読み込み
Private tsSql
Set tsSql = fs.OpenTextFile(WScript.Arguments(1))

Private sSql
sSql = ""
Do Until tsSql.AtEndOfStream
    sSql = sSql & tsSql.ReadLine & vbNewLine
Loop
tsSql.Close
Set tsSql = Nothing

'結果を CSV に保存
Private tsCsv
Set tsCsv = fs.OpenTextFile(WScript.Arguments(2), 2, True)  '2 = ForWriting

Dim rs
Set rs = CreateObject("ADODB.Recordset")
With rs
    .ActiveConnection = con
    .CursorType       = 0 'adOpenForwardOnly
    .LockType         = 1 'adLockReadOnly
    .Source           = sSql
    .Open
End With

If Not rs.EOF Then
    Dim i
    For i = 0 To rs.Fields.Count - 1
        tsCsv.Write rs.Fields(i).Name & ","
    Next
    tsCsv.WriteLine ""

    Do Until rs.EOF
        For i = 0 To rs.Fields.Count - 1
            tsCsv.Write """" & rs.Fields(i).Value & ""","
        Next
        tsCsv.WriteLine ""

        rs.MoveNext
    Loop
End If
rs.Close

tsCsv.Close
Set tsCsv = Nothing
Set fs = Nothing

'切断
con.Close
Set con = Nothing

実行形式

C:\>cscript c:\study\vbscript\chapter006\lesson002.vbs  c:\study\vbscript\chapte

r006\lesson002.con c:\study\vbscript\chapter006\lesson002.sql c:\study\vbscript\

chapter006\lesson002.csv //nologo

出力結果

lesson002.csv

table_name,ordinal_position,column_name,data_type,character_maximum_length,numeric_precision,numeric_precision_radix,numeric_scale,is_nullable,column_default,constraint_type,

"Address","1","AddressID","int","","10","10","0","NO","","PRIMARY KEY",

"Address","2","AddressLine1","nvarchar","60","","","","NO","","",

"Address","3","AddressLine2","nvarchar","60","","","","YES","","",

"Address","4","City","nvarchar","30","","","","NO","","",

"Address","5","StateProvinceID","int","","10","10","0","NO","","",

"Address","6","PostalCode","nvarchar","15","","","","NO","","",

"Address","7","rowguid","uniqueidentifier","","","","","NO","(newid())","",

"Address","8","ModifiedDate","datetime","","","","","NO","(getdate())","",

"AddressType","1","AddressTypeID","int","","10","10","0","NO","","PRIMARY KEY",

"AddressType","2","Name","nvarchar","50","","","","NO","","",