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;
ＨＴＭＬ出力用スクリプト
lesson003.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 '結果を HTML に保存 Private tsHtml Set tsHtml = 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 tsHtml.WriteLine "<!DOCTYPE HTML PUBLIC ""-//W3C//DTD HTML 4.01 Transitional//EN"">" tsHtml.WriteLine "<html>" tsHtml.WriteLine "<body>" tsHtml.WriteLine " <table border>" tsHtml.WriteLine " <tr>" tsHtml.Write " " Dim i For i = 0 To rs.Fields.Count - 1 tsHtml.Write "<th>" & rs.Fields(i).Name & "</th>" Next tsHtml.WriteLine "" tsHtml.WriteLine " </tr>" Do Until rs.EOF tsHtml.WriteLine " <tr>" tsHtml.Write " " For i = 0 To rs.Fields.Count - 1 tsHtml.Write "<td>" & rs.Fields(i).Value & "</td>" Next tsHtml.WriteLine "" tsHtml.WriteLine " </tr>" rs.MoveNext Loop tsHtml.WriteLine " </table>" tsHtml.WriteLine "</body>" tsHtml.WriteLine "</html>" End If rs.Close tsHtml.Close Set tsHtml = Nothing Set fs = Nothing '切断 con.Close Set con = Nothing
実行形式
C:\>cscript c:\study\vbscript\chapter006\lesson003.vbs c:\study\vbscript\chapte
r006\lesson002.con c:\study\vbscript\chapter006\lesson002.sql c:\study\vbscript\
chapter006\lesson003.html //nologo
出力結果
lesson003.html
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> <html> <body> <table border> <tr> <th>table_name</th><th>ordinal_position</th><th>column_name</th><th>data_type</th><th>character_maximum_length</th><th>numeric_precision</th><th>numeric_precision_radix</th><th>numeric_scale</th><th>is_nullable</th><th>column_default</th><th>constraint_type</th> </tr> <tr> <td>Address</td><td>1</td><td>AddressID</td><td>int</td><td></td><td>10</td><td>10</td><td>0</td><td>NO</td><td></td><td>PRIMARY KEY</td> </tr> <tr> <td>Address</td><td>2</td><td>AddressLine1</td><td>nvarchar</td><td>60</td><td></td><td></td><td></td><td>NO</td><td></td><td></td> </tr> <tr> <td>Address</td><td>3</td><td>AddressLine2</td><td>nvarchar</td><td>60</td><td></td><td></td><td></td><td>YES</td><td></td><td></td> </tr> <tr> <td>Address</td><td>4</td><td>City</td><td>nvarchar</td><td>30</td><td></td><td></td><td></td><td>NO</td><td></td><td></td> </tr> <tr> <td>Address</td><td>5</td><td>StateProvinceID</td><td>int</td><td></td><td>10</td><td>10</td><td>0</td><td>NO</td><td></td><td></td> </tr> <tr> <td>Address</td><td>6</td><td>PostalCode</td><td>nvarchar</td><td>15</td><td></td><td></td><td></td><td>NO</td><td></td><td></td> </tr> <tr> <td>Address</td><td>7</td><td>rowguid</td><td>uniqueidentifier</td><td></td><td></td><td></td><td></td><td>NO</td><td>(newid())</td><td></td> </tr> <tr> <td>Address</td><td>8</td><td>ModifiedDate</td><td>datetime</td><td></td><td></td><td></td><td></td><td>NO</td><td>(getdate())</td><td></td> </tr> <tr> <td>AddressType</td><td>1</td><td>AddressTypeID</td><td>int</td><td></td><td>10</td><td>10</td><td>0</td><td>NO</td><td></td><td>PRIMARY KEY</td> </tr> <tr> <td>AddressType</td><td>2</td><td>Name</td><td>nvarchar</td><td>50</td><td></td><td></td><td></td><td>NO</td><td></td><td></td> </tr> </table> </body> </html>
出力イメージ
lesson003.html
| 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 |
