SQLServer テーブル定義の取得 - SHUT THE FUCK UP AND WRITE SOME CODE

テーブル定義情報を取得して INSERT用ストアドプロシージャを作成する

テーブル定義取得用 SQL

lesson008.sql



select

    c.column_name

,   c.data_type

,   c.character_maximum_length

,   c.numeric_precision

,   c.numeric_scale

,   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       )

where

        (   c.table_name    =   '%table_name%'      )

order by

    c.ordinal_position

接続文字列

lesson004.con



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

ストアドプロシージャ作成用スクリプト

lesson008.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

'テーブル名を指定
Dim tableName
tableName = WScript.Arguments(3)
sSql = Replace(sSql, "%table_name%", tableName)

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

Dim fieldCount: fieldCount = -1
Dim fieldNames()
Dim fieldTypes()
Dim characterLengths()
Dim numericPrecisions()
Dim numericScales()
Dim constraints()

If Not rs.EOF Then
    Do Until rs.EOF
        fieldCount = fieldCount + 1

        ReDim Preserve fieldNames(fieldCount)
        ReDim Preserve fieldTypes(fieldCount)
        ReDim Preserve characterLengths(fieldCount)
        ReDim Preserve numericPrecisions(fieldCount)
        ReDim Preserve numericScales(fieldCount)
        ReDim Preserve constraints(fieldCount)

        fieldNames(fieldCount)        = rs.Fields("column_name").Value              & ""
        fieldTypes(fieldCount)        = rs.Fields("data_type").Value                & ""
        characterLengths(fieldCount)  = rs.Fields("character_maximum_length").Value & ""
        numericPrecisions(fieldCount) = rs.Fields("numeric_precision").Value        & ""
        numericScales(fieldCount)     = rs.Fields("numeric_scale").Value            & ""
        constraints(fieldCount)       = rs.Fields("constraint_type").Value          & ""

        rs.MoveNext
    Loop
End If
rs.Close

'切断
con.Close
Set con = Nothing

'INSERT 用 ストアードプロシージャ を 作成
If fieldCount >= 0 Then
    Dim tsTxt
    Set tsTxt = fs.OpenTextFile(WScript.Arguments(2), 2, True)  '2 = ForWriting

    tsTxt.WriteLine "drop procedure INSERT_" & tableName
    tsTxt.WriteLine "go"
    tsTxt.WriteLine ""

    tsTxt.WriteLine "create procedure INSERT_" & tableName & ""

    Dim i
    For i = 0 To fieldCount
        If i = 0 Then
            tsTxt.Write "    "
        Else
            tsTxt.Write ",   "
        End If

        tsTxt.Write "@" & Left(fieldNames(i) & Space(32), 32) & " " & fieldTypes(i)

        Select Case fieldTypes(i) & ""
            Case "decimal"
                tsTxt.Write " (" & numericPrecisions(i) & "," & numericScales(i) & ")"
            Case "nvarchar", "nchar", "varchar", "char"
                tsTxt.Write " (" & characterLengths(i) & ")"
        End Select

        tsTxt.WriteLine ""
    Next

    tsTxt.WriteLine "as"
    tsTxt.WriteLine ""
    tsTxt.WriteLine "set nocount on"
    tsTxt.WriteLine ""

    tsTxt.WriteLine "insert into"
    tsTxt.WriteLine "    " & tableName & ""
    tsTxt.WriteLine "("

    For i = 0 To fieldCount
        If i = 0 Then
            tsTxt.Write "    "
        Else
            tsTxt.Write ",   "
        End If

        tsTxt.WriteLine fieldNames(i)
    Next

    tsTxt.WriteLine ")"
    tsTxt.WriteLine "values"
    tsTxt.WriteLine "("

    For i = 0 To fieldCount
        If i = 0 Then
            tsTxt.Write "    "
        Else
            tsTxt.Write ",   "
        End If

        tsTxt.WriteLine "@" & fieldNames(i)
    Next

    tsTxt.WriteLine ")"
    tsTxt.WriteLine ""

    tsTxt.WriteLine "go"

    tsTxt.Close
    Set tsTxt = Nothing
End If

Set fs = Nothing

実行形式



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

r006\lesson004.con c:\study\vbscript\chapter006\lesson008.sql c:\study\vbscript\

chapter006\lesson008.txt DimAccount //nologo

出力結果

lesson008.txt

drop procedure INSERT_DimAccount
go

create procedure INSERT_DimAccount
@AccountKey int
, @ParentAccountKey int
, @AccountCodeAlternateKey int
, @ParentAccountCodeAlternateKey int
, @AccountDescription nvarchar (50)
, @AccountType nvarchar (50)
, @Operator nvarchar (50)
, @CustomMembers nvarchar (300)
, @ValueType nvarchar (50)
, @CustomMemberOptions nvarchar (200)
as

set nocount on

insert into
DimAccount
(
AccountKey
, ParentAccountKey
, AccountCodeAlternateKey
, ParentAccountCodeAlternateKey
, AccountDescription
, AccountType
, Operator
, CustomMembers
, ValueType
, CustomMemberOptions
)
values
(
@AccountKey
, @ParentAccountKey
, @AccountCodeAlternateKey
, @ParentAccountCodeAlternateKey
, @AccountDescription
, @AccountType
, @Operator
, @CustomMembers
, @ValueType
, @CustomMemberOptions
)