SQLServer テーブル定義の取得
不具合修正版
テーブル定義情報を取得してＥｘｃｅｌに出力する (テーブル別)
テーブル定義 取得用 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;
Ｅｘｃｅｌ出力用スクリプト
lesson015.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 Set fs = 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 '結果を Excel に保存 Dim excelApp Set excelApp = CreateObject("Excel.Application") excelApp.Visible = True excelApp.DisplayAlerts = False '警告メッセージをOFF 'ブックを書き込み用で開く Dim excelBook Set excelBook = excelApp.Workbooks.Add 'シートを１枚だけにする Dim iSheet For iSheet = excelBook.WorkSheets.Count To 2 Step -1 excelBook.WorkSheets(iSheet).Delete Next Dim tableNameList() Dim tableCount: tableCount = -1 Dim tableName: tableName = "" Dim iRow: iRow = 0 Dim iCol: iCol = 0 Dim excelSheet Do Until rs.EOF If tableName <> rs("table_name") & "" Then tableName = rs("table_name") & "" tableCount = tableCount + 1 ReDim Preserve tableNameList(tableCount) tableNameList(tableCount) = tableName excelBook.Sheets.Add , excelBook.WorkSheets(tableCount + 1) Set excelSheet = excelBook.WorkSheets(tableCount + 2) excelSheet.Name = tableName excelSheet.Cells(1, 1).Value = "No." excelSheet.Cells(1, 2).Value = "項目名" excelSheet.Cells(1, 3).Value = "" excelSheet.Cells(1, 4).Value = "型" excelSheet.Cells(1, 5).Value = "長さ" excelSheet.Cells(1, 6).Value = "小数部" excelSheet.Cells(1, 7).Value = "初期値" excelSheet.Cells(1, 8).Value = "必須" excelSheet.Cells(1, 9).Value = "PRIMARY KEY (CLUSTERED)" excelSheet.Cells(1, 10).Value = "PRIMARY KEY (NONCLUSTERED)" excelSheet.Cells(1, 11).Value = "UNIQUE KEY" excelSheet.Cells(1, 12).Value = "INDEX 1" excelSheet.Cells(1, 13).Value = "INDEX 2" excelSheet.Cells(1, 14).Value = "INDEX 3" excelSheet.Cells(1, 15).Value = "INDEX 4" excelSheet.Cells(1, 16).Value = "INDEX 5" excelSheet.Cells(1, 17).Value = "FOREIGN KEY" iRow = 1 End If iRow = iRow + 1 iCol = 0 'No. iCol = iCol + 1 excelSheet.Cells(iRow, iCol).Value = rs("COLUMN_ID") & "" '項目名 iCol = iCol + 1 excelSheet.Cells(iRow, iCol).Value = rs("column_name") & "" '項目説明 iCol = iCol + 1 excelSheet.Cells(iRow, iCol).Value = rs("COLUMN_COMMENT") & "" '型 iCol = iCol + 1 excelSheet.Cells(iRow, iCol).Value = rs("data_type") & "" '長さ iCol = iCol + 1 Select Case LCase(rs("data_type") & "") Case "nvarchar", "nchar", "varchar", "char" excelSheet.Cells(iRow, iCol).Value = rs("character_maximum_length") & "" Case "decimal" excelSheet.Cells(iRow, iCol).Value = rs("numeric_precision") & "" End Select '小数部 iCol = iCol + 1 Select Case LCase(rs("data_type") & "") Case "decimal" excelSheet.Cells(iRow, iCol).Value = rs("numeric_scale") & "" End Select '初期値 iCol = iCol + 1 excelSheet.Cells(iRow, iCol).Value = rs("column_default") & "" '必須 iCol = iCol + 1 Dim s: s = rs("is_nullable") & "" If UCase(s) = "NO" Then excelSheet.Cells(iRow, iCol).Value = "○" 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) iCol = iCol + 1 For idx = 1 To 5 If rs("INDEX" & idx & "_COLUMN_ID") & "" <> "0" Then If indexType(idx) = "CLUSTERED PRIMARY KEY" Then excelSheet.Cells(iRow, iCol).Value = rs("INDEX" & idx & "_COLUMN_ID") & "" Exit For End If End If Next 'PRIMARY KEY (NONCLUSTERED) iCol = iCol + 1 For idx = 1 To 5 If rs("INDEX" & idx & "_COLUMN_ID") & "" <> "0" Then If indexType(idx) = "NONCLUSTERED PRIMARY KEY" Then excelSheet.Cells(iRow, iCol).Value = rs("INDEX" & idx & "_COLUMN_ID") & "" Exit For End If End If Next 'UNIQUE KEY iCol = iCol + 1 For idx = 1 To 5 If rs("INDEX" & idx & "_COLUMN_ID") & "" <> "0" Then If indexType(idx) = "UNIQUE KEY" Then excelSheet.Cells(iRow, iCol).Value = rs("INDEX" & idx & "_COLUMN_ID") & "" Exit For End If End If Next 'OTHER INDEX For idx = 1 To 5 iCol = iCol + 1 If rs("INDEX" & idx & "_COLUMN_ID") & "" <> "0" Then If indexType(idx) = "" Then excelSheet.Cells(iRow, iCol).Value = rs("INDEX" & idx & "_COLUMN_ID") & "" End If End If Next 'FOREIGN KEY iCol = iCol + 1 If rs("FOREIGN_KEY") & "" <> "" Then excelSheet.Cells(iRow, iCol).Value = "○" End If rs.MoveNext Loop Set excelSheet = excelBook.WorkSheets(1) excelSheet.Name = "テーブル一覧" For iRow = 1 To tableCount + 1 excelSheet.Cells(iRow, 1).Value = tableNameList(iRow - 1) excelSheet.Hyperlinks.Add excelSheet.Cells(iRow, 1), "", tableNameList(iRow - 1) & "!A1" Next 'ブックを保存する excelBook.SaveAs(WScript.Arguments(2)) excelApp.Quit Set excelApp = Nothing End If rs.Close '切断 con.Close Set con = Nothing
実行形式
C:\>cscript c:\study\vbscript\chapter006\lesson015.vbs c:\study\vbscript\chapte
r006\lesson004.con c:\study\vbscript\chapter006\lesson014.sql c:\study\vbscript\
chapter006\lesson15.xls //nologo
出力イメージ
Sheet : "AdventureWorksDWBuildVersion"
| 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 | ||||||||||||||
Sheet : "DatabaseLog"
| 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 | ○ | |||||||||||||
Sheet : "DimAccount"
| 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 | |||||||||||||
Sheet : "DimCurrency"
| 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 | ○ | ||||||||||||
Sheet : "FactInternetSalesReason"
| 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 | ○ | |||||||||||
