SQLServer テーブル定義の取得
不具合あり！
テーブル定義情報を取得してＨＴＭＬに出力する (テーブル別)
テーブル定義 取得用 SQL
lesson004.sql
select
c.table_name
, c.ordinal_position
, c.column_name
, c.data_type
, c.character_maximum_length
, c.numeric_precision
, 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 )
)
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
接続文字列
lesson004.con
DRIVER={SQL Server};SERVER=xxxxxx;DataBase=adventureworksdw;UID=yyyyyy;PWD=zzzzzz;
ＨＴＭＬ出力用スクリプト
lesson004.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 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 '結果を HTML に保存 Dim folderName folderName = WScript.Arguments(2) If Right(folderName, 1) <> "\" Then folderName = folderName & "\" End If Dim tableNameList() Dim tableCount tableCount = -1 Dim tableName tableName = "" Dim tsHtml Do Until rs.EOF If tableName <> rs("table_name") & "" Then If tableName <> "" Then tsHtml.WriteLine " </table>" tsHtml.WriteLine "</body>" tsHtml.WriteLine "</html>" tsHtml.Close End If tableCount = tableCount + 1 ReDim Preserve tableNameList(tableCount) tableNameList(tableCount) = rs("table_name") & "" tableName = rs("table_name") & "" Set tsHtml = fs.OpenTextFile(folderName & tableName & ".html", 2, True) '2 = ForWriting tsHtml.WriteLine "<!DOCTYPE HTML PUBLIC ""-//W3C//DTD HTML 4.01 Transitional//EN"">" tsHtml.WriteLine "<html>" tsHtml.WriteLine "<head>" tsHtml.WriteLine " <title>" & tableName & "</title>" tsHtml.WriteLine "</head>" tsHtml.WriteLine "<body>" tsHtml.WriteLine " <h1>" & rs("table_name") & "</h2>" tsHtml.WriteLine " <table border>" tsHtml.WriteLine " <tr>" tsHtml.WriteLine " <th>No.</th>" tsHtml.WriteLine " <th>項目名</th>" tsHtml.WriteLine " <th>型</th>" tsHtml.WriteLine " <th>長さ</th>" tsHtml.WriteLine " <th>小数部</th>" tsHtml.WriteLine " <th>初期値</th>" tsHtml.WriteLine " <th>必須</th>" tsHtml.WriteLine " <th>制約</th>" tsHtml.WriteLine " </tr>" End If tsHtml.WriteLine " <tr>" 'No. tsHtml.WriteLine " <td>" & rs("ordinal_position") & "</td>" '項目名 Dim s tsHtml.WriteLine " <td>" & rs("column_name") & "</td>" '型 tsHtml.WriteLine " <td>" & rs("data_type") & "</td>" '長さ Select Case LCase(rs("data_type") & "") Case "nvarchar", "nchar", "varchar", "char" tsHtml.WriteLine " <td>" & rs("character_maximum_length") & "</td>" Case "decimal" tsHtml.WriteLine " <td>" & rs("numeric_precision") & "</td>" Case Else tsHtml.WriteLine " <td> </td>" End Select '小数部 Select Case LCase(rs("data_type") & "") Case "decimal" tsHtml.WriteLine " <td>" & rs("numeric_scale") & "</td>" Case Else tsHtml.WriteLine " <td> </td>" End Select '初期値 s = rs("column_default") & "" If s = "" Then tsHtml.WriteLine " <td> </td>" Else tsHtml.WriteLine " <td>" & s & "</td>" End If '必須 s = rs("is_nullable") & "" If UCase(s) = "NO" Then tsHtml.WriteLine " <td>○</td>" Else tsHtml.WriteLine " <td> </td>" End If '制約 tsHtml.WriteLine " <td>" & rs("constraint_type") & "</td>" tsHtml.WriteLine " </tr>" rs.MoveNext Loop tsHtml.WriteLine " </table>" tsHtml.WriteLine "</body>" tsHtml.WriteLine "</html>" tsHtml.Close Set tsHtml = Nothing End If rs.Close '切断 con.Close Set con = Nothing 'テーブル一覧 Set tsHtml = fs.OpenTextFile(folderName & "index.html", 2, True) '2 = ForWriting tsHtml.WriteLine "<!DOCTYPE HTML PUBLIC ""-//W3C//DTD HTML 4.01 Transitional//EN"">" tsHtml.WriteLine "<html>" tsHtml.WriteLine "<head>" tsHtml.WriteLine " <title>テーブル一覧</title>" tsHtml.WriteLine "</head>" tsHtml.WriteLine "<body>" tsHtml.WriteLine " <h1>テーブル一覧</h1>" tsHtml.WriteLine " <table border>" Private i For i = 0 To tableCount tsHtml.WriteLine " <tr>" tsHtml.WriteLine " <td><a href='" & tableNameList(i) & ".html'>" & tableNameList(i) & "</a></td>" tsHtml.WriteLine " </tr>" Next tsHtml.WriteLine " </table>" tsHtml.WriteLine "</body>" tsHtml.WriteLine "</html>" tsHtml.Close Set tsHtml = Nothing Set fs = Nothing
実行形式
C:\>cscript c:\study\vbscript\chapter006\lesson004.vbs c:\study\vbscript\chapte
r006\lesson004.con c:\study\vbscript\chapter006\lesson004.sql c:\study\vbscript\
chapter006\html //nologo
出力結果
index.html
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> <html> <head> <title>テーブル一覧</title> </head> <body> <h1>テーブル一覧</h1> <table border> <tr> <td><a href='AdventureWorksDWBuildVersion.html'>AdventureWorksDWBuildVersion</a></td> </tr> <tr> <td><a href='DatabaseLog.html'>DatabaseLog</a></td> </tr> <tr> <td><a href='DimAccount.html'>DimAccount</a></td> </tr> <tr> <td><a href='DimCurrency.html'>DimCurrency</a></td> </tr> </table> </body> </html>
AdventureWorksDWBuildVersion.html
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> <html> <head> <title>AdventureWorksDWBuildVersion</title> </head> <body> <h1>AdventureWorksDWBuildVersion</h2> <table border> <tr> <th>No.</th> <th>項目名</th> <th>型</th> <th>長さ</th> <th>小数部</th> <th>初期値</th> <th>必須</th> <th>制約</th> </tr> <tr> <td>1</td> <td>DBVersion</td> <td>nvarchar</td> <td>50</td> <td> </td> <td> </td> <td> </td> <td></td> </tr> <tr> <td>2</td> <td>VersionDate</td> <td>datetime</td> <td> </td> <td> </td> <td> </td> <td> </td> <td></td> </tr> </table> </body> </html>
DatabaseLog.html
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> <html> <head> <title>DatabaseLog</title> </head> <body> <h1>DatabaseLog</h2> <table border> <tr> <th>No.</th> <th>項目名</th> <th>型</th> <th>長さ</th> <th>小数部</th> <th>初期値</th> <th>必須</th> <th>制約</th> </tr> <tr> <td>1</td> <td>DatabaseLogID</td> <td>int</td> <td> </td> <td> </td> <td> </td> <td>○</td> <td></td> </tr> <tr> <td>2</td> <td>PostTime</td> <td>datetime</td> <td> </td> <td> </td> <td> </td> <td>○</td> <td></td> </tr> <tr> <td>3</td> <td>DatabaseUser</td> <td>nvarchar</td> <td>128</td> <td> </td> <td> </td> <td>○</td> <td></td> </tr> <tr> <td>4</td> <td>Event</td> <td>nvarchar</td> <td>128</td> <td> </td> <td> </td> <td>○</td> <td></td> </tr> <tr> <td>5</td> <td>Schema</td> <td>nvarchar</td> <td>128</td> <td> </td> <td> </td> <td> </td> <td></td> </tr> <tr> <td>6</td> <td>Object</td> <td>nvarchar</td> <td>128</td> <td> </td> <td> </td> <td> </td> <td></td> </tr> <tr> <td>7</td> <td>TSQL</td> <td>nvarchar</td> <td>-1</td> <td> </td> <td> </td> <td>○</td> <td></td> </tr> <tr> <td>8</td> <td>XmlEvent</td> <td>xml</td> <td> </td> <td> </td> <td> </td> <td>○</td> <td></td> </tr> </table> </body> </html>
DimAccount.html
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> <html> <head> <title>DimAccount</title> </head> <body> <h1>DimAccount</h2> <table border> <tr> <th>No.</th> <th>項目名</th> <th>型</th> <th>長さ</th> <th>小数部</th> <th>初期値</th> <th>必須</th> <th>制約</th> </tr> <tr> <td>1</td> <td>AccountKey</td> <td>int</td> <td> </td> <td> </td> <td> </td> <td>○</td> <td>PRIMARY KEY</td> </tr> <tr> <td>2</td> <td>ParentAccountKey</td> <td>int</td> <td> </td> <td> </td> <td> </td> <td> </td> <td>FOREIGN KEY</td> </tr> <tr> <td>3</td> <td>AccountCodeAlternateKey</td> <td>int</td> <td> </td> <td> </td> <td> </td> <td> </td> <td></td> </tr> <tr> <td>4</td> <td>ParentAccountCodeAlternateKey</td> <td>int</td> <td> </td> <td> </td> <td> </td> <td> </td> <td></td> </tr> <tr> <td>5</td> <td>AccountDescription</td> <td>nvarchar</td> <td>50</td> <td> </td> <td> </td> <td> </td> <td></td> </tr> <tr> <td>6</td> <td>AccountType</td> <td>nvarchar</td> <td>50</td> <td> </td> <td> </td> <td> </td> <td></td> </tr> <tr> <td>7</td> <td>Operator</td> <td>nvarchar</td> <td>50</td> <td> </td> <td> </td> <td> </td> <td></td> </tr> <tr> <td>8</td> <td>CustomMembers</td> <td>nvarchar</td> <td>300</td> <td> </td> <td> </td> <td> </td> <td></td> </tr> <tr> <td>9</td> <td>ValueType</td> <td>nvarchar</td> <td>50</td> <td> </td> <td> </td> <td> </td> <td></td> </tr> <tr> <td>10</td> <td>CustomMemberOptions</td> <td>nvarchar</td> <td>200</td> <td> </td> <td> </td> <td> </td> <td></td> </tr> </table> </body> </html>
DimCurrency.html
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> <html> <head> <title>DimCurrency</title> </head> <body> <h1>DimCurrency</h2> <table border> <tr> <th>No.</th> <th>項目名</th> <th>型</th> <th>長さ</th> <th>小数部</th> <th>初期値</th> <th>必須</th> <th>制約</th> </tr> <tr> <td>1</td> <td>CurrencyKey</td> <td>int</td> <td> </td> <td> </td> <td> </td> <td>○</td> <td>PRIMARY KEY</td> </tr> <tr> <td>2</td> <td>CurrencyAlternateKey</td> <td>nchar</td> <td>3</td> <td> </td> <td> </td> <td>○</td> <td>UNIQUE</td> </tr> <tr> <td>3</td> <td>CurrencyName</td> <td>nvarchar</td> <td>50</td> <td> </td> <td> </td> <td>○</td> <td></td> </tr> </table> </body> </html>
出力イメージ
index.html
| AdventureWorksDWBuildVersion |
| DatabaseLog |
| DimAccount |
| DimCurrency |
AdventureWorksDWBuildVersion.html
| No. | 項目名 | 型 | 長さ | 小数部 | 初期値 | 必須 | 制約 |
|---|---|---|---|---|---|---|---|
| 1 | DBVersion | nvarchar | 50 | ||||
| 2 | VersionDate | datetime |
DatabaseLog.html
| No. | 項目名 | 型 | 長さ | 小数部 | 初期値 | 必須 | 制約 |
|---|---|---|---|---|---|---|---|
| 1 | DatabaseLogID | int | ○ | ||||
| 2 | PostTime | datetime | ○ | ||||
| 3 | DatabaseUser | nvarchar | 128 | ○ | |||
| 4 | Event | nvarchar | 128 | ○ | |||
| 5 | Schema | nvarchar | 128 | ||||
| 6 | Object | nvarchar | 128 | ||||
| 7 | TSQL | nvarchar | -1 | ○ | |||
| 8 | XmlEvent | xml | ○ |
DimAccount.html
| No. | 項目名 | 型 | 長さ | 小数部 | 初期値 | 必須 | 制約 |
|---|---|---|---|---|---|---|---|
| 1 | AccountKey | int | ○ | PRIMARY KEY | |||
| 2 | ParentAccountKey | int | FOREIGN KEY | ||||
| 3 | AccountCodeAlternateKey | int | |||||
| 4 | ParentAccountCodeAlternateKey | int | |||||
| 5 | AccountDescription | nvarchar | 50 | ||||
| 6 | AccountType | nvarchar | 50 | ||||
| 7 | Operator | nvarchar | 50 | ||||
| 8 | CustomMembers | nvarchar | 300 | ||||
| 9 | ValueType | nvarchar | 50 | ||||
| 10 | CustomMemberOptions | nvarchar | 200 |
DimCurrency.html
| No. | 項目名 | 型 | 長さ | 小数部 | 初期値 | 必須 | 制約 |
|---|---|---|---|---|---|---|---|
| 1 | CurrencyKey | int | ○ | PRIMARY KEY | |||
| 2 | CurrencyAlternateKey | nchar | 3 | ○ | UNIQUE | ||
| 3 | CurrencyName | nvarchar | 50 | ○ |
不具合発見！
FactInternetSalesReason.html
| No. | 項目名 | 型 | 長さ | 小数部 | 初期値 | 必須 | 制約 |
|---|---|---|---|---|---|---|---|
| 1 | SalesOrderNumber | nvarchar | 20 | ○ | UNIQUE | ||
| 1 | SalesOrderNumber | nvarchar | 20 | ○ | FOREIGN KEY | ||
| 2 | SalesOrderLineNumber | tinyint | ○ | UNIQUE | |||
| 2 | SalesOrderLineNumber | tinyint | ○ | FOREIGN KEY | |||
| 3 | SalesReasonKey | int | ○ | UNIQUE | |||
| 3 | SalesReasonKey | int | ○ | FOREIGN KEY |
