回答編集履歴
2
DOCS の tv を修正
test
CHANGED
|
@@ -130,11 +130,11 @@
|
|
|
130
130
|
|
|
131
131
|
UNION ALL
|
|
132
132
|
|
|
133
|
-
SELECT DISTINCT TableId, TableName,
|
|
133
|
+
SELECT DISTINCT TableId, TableName, tv, -8 AS ColumnId, '項目名', 'データ型', 'PKEY', 'NOT NULL', 'IDENTITY', '照合順序', 'コメント', TableComment FROM TBLS --見出し行
|
|
134
134
|
|
|
135
135
|
UNION ALL
|
|
136
136
|
|
|
137
|
-
SELECT DISTINCT TableId, TableName,
|
|
137
|
+
SELECT DISTINCT TableId, TableName, tv, 9999 AS ColumnId, '', '', '', '', '', '', '', '' FROM TBLS --空行用
|
|
138
138
|
|
|
139
139
|
)
|
|
140
140
|
|
1
sys\.tables だけでなく sys\.views も参照するよう修正
test
CHANGED
|
@@ -17,6 +17,8 @@
|
|
|
17
17
|
tbl.object_id AS TableId
|
|
18
18
|
|
|
19
19
|
, tbl.name AS TableName
|
|
20
|
+
|
|
21
|
+
, tbl.tv
|
|
20
22
|
|
|
21
23
|
, col.column_id AS ColumnId
|
|
22
24
|
|
|
@@ -90,7 +92,19 @@
|
|
|
90
92
|
|
|
91
93
|
, tep.value AS TableComment
|
|
92
94
|
|
|
95
|
+
|
|
96
|
+
|
|
93
|
-
|
|
97
|
+
--FROM sys.tables tbl
|
|
98
|
+
|
|
99
|
+
FROM (
|
|
100
|
+
|
|
101
|
+
SELECT 'T' AS [tv], object_id, name FROM sys.tables UNION ALL
|
|
102
|
+
|
|
103
|
+
SELECT 'V' AS [tv], object_id, name FROM sys.views
|
|
104
|
+
|
|
105
|
+
) tbl
|
|
106
|
+
|
|
107
|
+
|
|
94
108
|
|
|
95
109
|
LEFT JOIN sys.columns col ON (col.object_id = tbl.object_id)
|
|
96
110
|
|
|
@@ -112,21 +126,23 @@
|
|
|
112
126
|
|
|
113
127
|
UNION ALL
|
|
114
128
|
|
|
115
|
-
SELECT DISTINCT TableId, TableName, -9 AS ColumnId, '', '', '', '', '', '', '', TableComment FROM TBLS --テーブル名行
|
|
129
|
+
SELECT DISTINCT TableId, TableName, tv, -9 AS ColumnId, '', '', '', '', '', '', '', TableComment FROM TBLS --テーブル名行
|
|
116
130
|
|
|
117
131
|
UNION ALL
|
|
118
132
|
|
|
119
|
-
SELECT DISTINCT TableId, TableName, -8 AS ColumnId, '項目名', 'データ型', 'PKEY', 'NOT NULL', 'IDENTITY', '照合順序', 'コメント', TableComment FROM TBLS --見出し行
|
|
133
|
+
SELECT DISTINCT TableId, TableName, '', -8 AS ColumnId, '項目名', 'データ型', 'PKEY', 'NOT NULL', 'IDENTITY', '照合順序', 'コメント', TableComment FROM TBLS --見出し行
|
|
120
134
|
|
|
121
135
|
UNION ALL
|
|
122
136
|
|
|
123
|
-
SELECT DISTINCT TableId, TableName, 9999 AS ColumnId, '', '', '', '', '', '', '', '' FROM TBLS --空行用
|
|
137
|
+
SELECT DISTINCT TableId, TableName, '', 9999 AS ColumnId, '', '', '', '', '', '', '', '' FROM TBLS --空行用
|
|
124
138
|
|
|
125
139
|
)
|
|
126
140
|
|
|
127
141
|
SELECT
|
|
128
142
|
|
|
129
143
|
CASE WHEN ColumnId = -9 THEN TableName ELSE '' END [テーブル名]
|
|
144
|
+
|
|
145
|
+
, CASE WHEN ColumnId = -9 THEN tv ELSE '' END [tv]
|
|
130
146
|
|
|
131
147
|
, ColumnName AS [項目名]
|
|
132
148
|
|