SQLServer テーブル定義の取得
不具合修正版
テーブル定義情報を取得してHTMLに出力する (テーブル別)
テーブル定義 取得用 SQL
lesson014.sql
select
C.NAME SCHEMA_NAME
, K.VALUE TABLE_COMMENT
, B.NAME TABLE_NAME
, A.COLUMN_ID
, A.NAME COLUMN_NAME
, L.VALUE COLUMN_COMMENT
, D.DATA_TYPE
, D.CHARACTER_MAXIMUM_LENGTH
, D.NUMERIC_PRECISION
, D.NUMERIC_SCALE
, case D.IS_NULLABLE when 'YES' then '' else 'NO' end IS_NULLABLE
, isnull(D.COLUMN_DEFAULT, '') COLUMN_DEFAULT
--
, isnull(G1.CONSTRAINT_NAME, '') PRIMARY_KEY
, isnull(G2.CONSTRAINT_NAME, '') FOREIGN_KEY
, isnull(G3.CONSTRAINT_NAME, '') UNIQUE_KEY
--
, isnull(J1.NAME, '') INDEX1_NAME
, isnull(J1.TYPE_DESC, '') INDEX1_TYPE
, isnull(J1.INDEX_COLUMN_ID, '') INDEX1_COLUMN_ID
, isnull(M1.VALUE, '') INDEX1_COMMENT
--
, isnull(J2.NAME, '') INDEX2_NAME
, isnull(J2.TYPE_DESC, '') INDEX2_TYPE
, isnull(J2.INDEX_COLUMN_ID, '') INDEX2_COLUMN_ID
, isnull(M2.VALUE, '') INDEX2_COMMENT
--
, isnull(J3.NAME, '') INDEX3_NAME
, isnull(J3.TYPE_DESC, '') INDEX3_TYPE
, isnull(J3.INDEX_COLUMN_ID, '') INDEX3_COLUMN_ID
, isnull(M3.VALUE, '') INDEX3_COMMENT
--
, isnull(J4.NAME, '') INDEX4_NAME
, isnull(J4.TYPE_DESC, '') INDEX4_TYPE
, isnull(J4.INDEX_COLUMN_ID, '') INDEX4_COLUMN_ID
, isnull(M4.VALUE, '') INDEX4_COMMENT
--
, isnull(J5.NAME, '') INDEX5_NAME
, isnull(J5.TYPE_DESC, '') INDEX5_TYPE
, isnull(J5.INDEX_COLUMN_ID, '') INDEX5_COLUMN_ID
, isnull(M5.VALUE, '') INDEX5_COMMENT
--
from
sys.columns A
--
inner join
sys.tables B
on
B.object_id = A.object_id
inner join
sys.schemas C
on
C.SCHEMA_ID = B.SCHEMA_ID
--
inner join
information_schema.columns D
on
D.TABLE_SCHEMA = C.NAME
and D.TABLE_NAME = B.NAME
and D.COLUMN_NAME = A.NAME
--
left join
(
select
F1.TABLE_CATALOG
, F1.TABLE_SCHEMA
, F1.TABLE_NAME
, F1.COLUMN_NAME
, E1.CONSTRAINT_NAME
, E1.CONSTRAINT_TYPE
from
information_schema.table_constraints E1
inner join
information_schema.key_column_usage F1
on
F1.CONSTRAINT_NAME = E1.CONSTRAINT_NAME
where
E1.CONSTRAINT_TYPE = 'PRIMARY KEY'
)
G1
on
( G1.TABLE_CATALOG = D.TABLE_CATALOG )
and ( G1.TABLE_SCHEMA = D.TABLE_SCHEMA )
and ( G1.TABLE_NAME = D.TABLE_NAME )
and ( G1.COLUMN_NAME = D.COLUMN_NAME )
--
left join
(
select
F2.TABLE_CATALOG
, F2.TABLE_SCHEMA
, F2.TABLE_NAME
, F2.COLUMN_NAME
, E2.CONSTRAINT_NAME
, E2.CONSTRAINT_TYPE
from
information_schema.table_constraints E2
inner join
information_schema.key_column_usage F2
on
F2.CONSTRAINT_NAME = E2.CONSTRAINT_NAME
where
E2.CONSTRAINT_TYPE = 'FOREIGN KEY'
)
G2
on
( G2.TABLE_CATALOG = D.TABLE_CATALOG )
and ( G2.TABLE_SCHEMA = D.TABLE_SCHEMA )
and ( G2.TABLE_NAME = D.TABLE_NAME )
and ( G2.COLUMN_NAME = D.COLUMN_NAME )
--
left join
(
select
F3.TABLE_CATALOG
, F3.TABLE_SCHEMA
, F3.TABLE_NAME
, F3.COLUMN_NAME
, E3.CONSTRAINT_NAME
, E3.CONSTRAINT_TYPE
from
information_schema.table_constraints E3
inner join
information_schema.key_column_usage F3
on
F3.CONSTRAINT_NAME = E3.CONSTRAINT_NAME
where
E3.CONSTRAINT_TYPE = 'UNIQUE'
)
G3
on
( G3.TABLE_CATALOG = D.TABLE_CATALOG )
and ( G3.TABLE_SCHEMA = D.TABLE_SCHEMA )
and ( G3.TABLE_NAME = D.TABLE_NAME )
and ( G3.COLUMN_NAME = D.COLUMN_NAME )
--
left join
(
select
H1.object_id
, H1.INDEX_ID
, H1.NAME
, H1.TYPE_DESC
, I1.COLUMN_ID
, I1.INDEX_COLUMN_ID
from
sys.indexes H1
inner join
sys.index_columns I1
on
( I1.object_id = H1.object_id )
and ( I1.INDEX_ID = H1.INDEX_ID )
where
( H1.INDEX_ID = 1 )
)
J1
on
J1.object_id = A.object_id
and J1.COLUMN_ID = A.COLUMN_ID
--
left join
(
select
H2.object_id
, H2.INDEX_ID
, H2.NAME
, H2.TYPE_DESC
, I2.COLUMN_ID
, I2.INDEX_COLUMN_ID
from
sys.indexes H2
inner join
sys.index_columns I2
on
( I2.object_id = H2.object_id )
and ( I2.INDEX_ID = H2.INDEX_ID )
where
( H2.INDEX_ID = 2 )
)
J2
on
J2.object_id = A.object_id
and J2.COLUMN_ID = A.COLUMN_ID
--
left join
(
select
H3.object_id
, H3.INDEX_ID
, H3.NAME
, H3.TYPE_DESC
, I3.COLUMN_ID
, I3.INDEX_COLUMN_ID
from
sys.indexes H3
inner join
sys.index_columns I3
on
( I3.object_id = H3.object_id )
and ( I3.INDEX_ID = H3.INDEX_ID )
where
( H3.INDEX_ID = 3 )
)
J3
on
J3.object_id = A.object_id
and J3.COLUMN_ID = A.COLUMN_ID
--
left join
(
select
H4.object_id
, H4.INDEX_ID
, H4.NAME
, H4.TYPE_DESC
, I4.COLUMN_ID
, I4.INDEX_COLUMN_ID
from
sys.indexes H4
inner join
sys.index_columns I4
on
( I4.object_id = H4.object_id )
and ( I4.INDEX_ID = H4.INDEX_ID )
where
( H4.INDEX_ID = 4 )
)
J4
on
J4.object_id = A.object_id
and J4.COLUMN_ID = A.COLUMN_ID
--
left join
(
select
H5.object_id
, H5.INDEX_ID
, H5.NAME
, H5.TYPE_DESC
, I5.COLUMN_ID
, I5.INDEX_COLUMN_ID
from
sys.indexes H5
inner join
sys.index_columns I5
on
( I5.object_id = H5.object_id )
and ( I5.INDEX_ID = H5.INDEX_ID )
where
( H5.INDEX_ID = 5 )
)
J5
on
J5.object_id = A.object_id
and J5.COLUMN_ID = A.COLUMN_ID
--
left join
sys.extended_properties K
on
( K.CLASS = 1 )
and ( K.MAJOR_ID = B.object_id )
and ( K.MINOR_ID = 0 )
--
left join
sys.extended_properties L
on
( L.CLASS = 1 )
and ( L.MAJOR_ID = B.object_id )
and ( L.MINOR_ID = A.COLUMN_ID )
--
left join
sys.extended_properties M1
on
( M1.CLASS = 7 )
and ( M1.MAJOR_ID = B.object_id )
and ( M1.MINOR_ID = J1.INDEX_ID )
--
left join
sys.extended_properties M2
on
( M2.CLASS = 7 )
and ( M2.MAJOR_ID = B.object_id )
and ( M2.MINOR_ID = J2.INDEX_ID )
--
left join
sys.extended_properties M3
on
( M3.CLASS = 7 )
and ( M3.MAJOR_ID = B.object_id )
and ( M3.MINOR_ID = J3.INDEX_ID )
--
left join
sys.extended_properties M4
on
( M4.CLASS = 7 )
and ( M4.MAJOR_ID = B.object_id )
and ( M4.MINOR_ID = J4.INDEX_ID )
--
left join
sys.extended_properties M5
on
( M5.CLASS = 7 )
and ( M5.MAJOR_ID = B.object_id )
and ( M5.MINOR_ID = J5.INDEX_ID )
--
where
( B.TYPE_DESC = 'USER_TABLE' )
order by
B.NAME
, A.COLUMN_ID
接続文字列
lesson004.con
DRIVER={SQL Server};SERVER=xxxxxx;DataBase=adventureworksdw;UID=yyyyyy;PWD=zzzzzz;
HTML出力用スクリプト
lesson014.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") & "</h1>" Dim s: s = rs("TABLE_COMMENT") & "" If s <> "" Then tsHtml.WriteLine " <h2>" & s & "</h2>" End If tsHtml.WriteLine " <table border>" tsHtml.WriteLine " <tr>" tsHtml.WriteLine " <th>No.</th>" tsHtml.WriteLine " <th colspan='2'>項目名</th>" tsHtml.WriteLine " <th>型</th>" tsHtml.WriteLine " <th>長さ</th>" tsHtml.WriteLine " <th>小数部</th>" tsHtml.WriteLine " <th>初期値</th>" tsHtml.WriteLine " <th>必須</th>" tsHtml.WriteLine " <th>PRIMARY KEY<BR>(CLUSTERED)</th>" tsHtml.WriteLine " <th>PRIMARY KEY<BR>(NONCLUSTERED)</th>" tsHtml.WriteLine " <th>UNIQUE<BR>KEY</th>" tsHtml.WriteLine " <th>INDEX<BR>1</th>" tsHtml.WriteLine " <th>INDEX<BR>2</th>" tsHtml.WriteLine " <th>INDEX<BR>3</th>" tsHtml.WriteLine " <th>INDEX<BR>4</th>" tsHtml.WriteLine " <th>INDEX<BR>5</th>" tsHtml.WriteLine " <th>FOREIGN<BR>KEY</th>" tsHtml.WriteLine " </tr>" End If tsHtml.WriteLine " <tr>" 'No. tsHtml.WriteLine " <td>" & rs("COLUMN_ID") & "</td>" '項目名 s = rs("COLUMN_COMMENT") & "" If s = "" Then tsHtml.WriteLine " <td colspan='2'>" & rs("column_name") & "</td>" Else tsHtml.WriteLine " <td>" & rs("column_name") & "</td>" tsHtml.WriteLine " <td>" & rs("COLUMN_COMMENT") & "</td>" End If '型 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 'INDEX Dim indexType: ReDim indexType(5) Dim idx For idx = 1 To 5 If rs("INDEX" & idx & "_NAME") & "" <> "" Then If rs("INDEX" & idx & "_NAME") & "" = rs("PRIMARY_KEY") & "" Then indexType(idx) = rs("INDEX" & idx & "_TYPE") & " PRIMARY KEY" ElseIf rs("INDEX" & idx & "_NAME") & "" = rs("UNIQUE_KEY") & "" Then indexType(idx) = "UNIQUE KEY" Else indexType(idx) = "" End If End If Next 'PRIMARY KEY (CLUSTERED) Dim match: match = False For idx = 1 To 5 If rs("INDEX" & idx & "_COLUMN_ID") & "" <> "0" Then If indexType(idx) = "CLUSTERED PRIMARY KEY" Then tsHtml.WriteLine " <td>" & rs("INDEX" & idx & "_COLUMN_ID") & "</td>" match = True Exit For End If End If Next If Not match Then tsHtml.WriteLine " <td> </td>" End If 'PRIMARY KEY (NONCLUSTERED) match = False For idx = 1 To 5 If rs("INDEX" & idx & "_COLUMN_ID") & "" <> "0" Then If indexType(idx) = "NONCLUSTERED PRIMARY KEY" Then tsHtml.WriteLine " <td>" & rs("INDEX" & idx & "_COLUMN_ID") & "</td>" match = True Exit For End If End If Next If Not match Then tsHtml.WriteLine " <td> </td>" End If 'UNIQUE KEY match = False For idx = 1 To 5 If rs("INDEX" & idx & "_COLUMN_ID") & "" <> "0" Then If indexType(idx) = "UNIQUE KEY" Then tsHtml.WriteLine " <td>" & rs("INDEX" & idx & "_COLUMN_ID") & "</td>" match = True Exit For End If End If Next If Not match Then tsHtml.WriteLine " <td> </td>" End If 'OTHER INDEX For idx = 1 To 5 If rs("INDEX" & idx & "_COLUMN_ID") & "" <> "0" Then If indexType(idx) = "" Then tsHtml.WriteLine " <td>" & rs("INDEX" & idx & "_COLUMN_ID") & "</td>" Else tsHtml.WriteLine " <td> </td>" End If Else tsHtml.WriteLine " <td> </td>" End If Next 'FOREIGN KEY s = rs("FOREIGN_KEY") & "" If s = "" Then tsHtml.WriteLine " <td> </td>" Else tsHtml.WriteLine " <td>○</td>" End If 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\lesson014.vbs c:\study\vbscript\chapte
r006\lesson004.con c:\study\vbscript\chapter006\lesson014.sql c:\study\vbscript\
chapter006\html //nologo
出力イメージ
AdventureWorksDWBuildVersion.html
No. | 項目名 | 型 | 長さ | 小数部 | 初期値 | 必須 | PRIMARY KEY (CLUSTERED) |
PRIMARY KEY (NONCLUSTERED) |
UNIQUE KEY |
INDEX 1 |
INDEX 2 |
INDEX 3 |
INDEX 4 |
INDEX 5 |
FOREIGN KEY |
|
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
1 | DBVersion | nvarchar | 50 | |||||||||||||
2 | VersionDate | datetime |
DatabaseLog.html
No. | 項目名 | 型 | 長さ | 小数部 | 初期値 | 必須 | PRIMARY KEY (CLUSTERED) |
PRIMARY KEY (NONCLUSTERED) |
UNIQUE KEY |
INDEX 1 |
INDEX 2 |
INDEX 3 |
INDEX 4 |
INDEX 5 |
FOREIGN KEY |
|
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
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. | 項目名 | 型 | 長さ | 小数部 | 初期値 | 必須 | PRIMARY KEY (CLUSTERED) |
PRIMARY KEY (NONCLUSTERED) |
UNIQUE KEY |
INDEX 1 |
INDEX 2 |
INDEX 3 |
INDEX 4 |
INDEX 5 |
FOREIGN KEY |
|
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
1 | AccountKey | int | ○ | 1 | ||||||||||||
2 | ParentAccountKey | int | ○ | |||||||||||||
3 | AccountCodeAlternateKey | int | 1 | |||||||||||||
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. | 項目名 | 型 | 長さ | 小数部 | 初期値 | 必須 | PRIMARY KEY (CLUSTERED) |
PRIMARY KEY (NONCLUSTERED) |
UNIQUE KEY |
INDEX 1 |
INDEX 2 |
INDEX 3 |
INDEX 4 |
INDEX 5 |
FOREIGN KEY |
|
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
1 | CurrencyKey | int | ○ | 1 | ||||||||||||
2 | CurrencyAlternateKey | nchar | 3 | ○ | 1 | |||||||||||
3 | CurrencyName | nvarchar | 50 | ○ |
FactInternetSalesReason.html
No. | 項目名 | 型 | 長さ | 小数部 | 初期値 | 必須 | PRIMARY KEY (CLUSTERED) |
PRIMARY KEY (NONCLUSTERED) |
UNIQUE KEY |
INDEX 1 |
INDEX 2 |
INDEX 3 |
INDEX 4 |
INDEX 5 |
FOREIGN KEY |
|
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
1 | SalesOrderNumber | nvarchar | 20 | ○ | 1 | ○ | ||||||||||
2 | SalesOrderLineNumber | tinyint | ○ | 2 | ○ | |||||||||||
3 | SalesReasonKey | int | ○ | 3 | ○ |