回答編集履歴
2
DOCS の tv を修正
answer
CHANGED
|
@@ -64,9 +64,9 @@
|
|
|
64
64
|
UNION ALL
|
|
65
65
|
SELECT DISTINCT TableId, TableName, tv, -9 AS ColumnId, '', '', '', '', '', '', '', TableComment FROM TBLS --テーブル名行
|
|
66
66
|
UNION ALL
|
|
67
|
-
SELECT DISTINCT TableId, TableName,
|
|
67
|
+
SELECT DISTINCT TableId, TableName, tv, -8 AS ColumnId, '項目名', 'データ型', 'PKEY', 'NOT NULL', 'IDENTITY', '照合順序', 'コメント', TableComment FROM TBLS --見出し行
|
|
68
68
|
UNION ALL
|
|
69
|
-
SELECT DISTINCT TableId, TableName,
|
|
69
|
+
SELECT DISTINCT TableId, TableName, tv, 9999 AS ColumnId, '', '', '', '', '', '', '', '' FROM TBLS --空行用
|
|
70
70
|
)
|
|
71
71
|
SELECT
|
|
72
72
|
CASE WHEN ColumnId = -9 THEN TableName ELSE '' END [テーブル名]
|
1
sys\.tables だけでなく sys\.views も参照するよう修正
answer
CHANGED
|
@@ -8,6 +8,7 @@
|
|
|
8
8
|
SELECT
|
|
9
9
|
tbl.object_id AS TableId
|
|
10
10
|
, tbl.name AS TableName
|
|
11
|
+
, tbl.tv
|
|
11
12
|
, col.column_id AS ColumnId
|
|
12
13
|
, col.name AS ColumnName
|
|
13
14
|
|
|
@@ -44,7 +45,13 @@
|
|
|
44
45
|
-- コメント
|
|
45
46
|
, ep.value AS ColumnComment
|
|
46
47
|
, tep.value AS TableComment
|
|
48
|
+
|
|
47
|
-
|
|
49
|
+
--FROM sys.tables tbl
|
|
50
|
+
FROM (
|
|
51
|
+
SELECT 'T' AS [tv], object_id, name FROM sys.tables UNION ALL
|
|
52
|
+
SELECT 'V' AS [tv], object_id, name FROM sys.views
|
|
53
|
+
) tbl
|
|
54
|
+
|
|
48
55
|
LEFT JOIN sys.columns col ON (col.object_id = tbl.object_id)
|
|
49
56
|
LEFT JOIN sys.types dtp ON (dtp.system_type_id = col.system_type_id AND dtp.user_type_id = col.user_type_id)
|
|
50
57
|
LEFT JOIN sys.identity_columns idt ON (idt.object_id = col.object_id AND idt.column_id = col.column_id)
|
|
@@ -55,14 +62,15 @@
|
|
|
55
62
|
, DOCS AS (
|
|
56
63
|
SELECT * FROM TBLS
|
|
57
64
|
UNION ALL
|
|
58
|
-
SELECT DISTINCT TableId, TableName, -9 AS ColumnId, '', '', '', '', '', '', '', TableComment FROM TBLS --テーブル名行
|
|
65
|
+
SELECT DISTINCT TableId, TableName, tv, -9 AS ColumnId, '', '', '', '', '', '', '', TableComment FROM TBLS --テーブル名行
|
|
59
66
|
UNION ALL
|
|
60
|
-
SELECT DISTINCT TableId, TableName, -8 AS ColumnId, '項目名', 'データ型', 'PKEY', 'NOT NULL', 'IDENTITY', '照合順序', 'コメント', TableComment FROM TBLS --見出し行
|
|
67
|
+
SELECT DISTINCT TableId, TableName, '', -8 AS ColumnId, '項目名', 'データ型', 'PKEY', 'NOT NULL', 'IDENTITY', '照合順序', 'コメント', TableComment FROM TBLS --見出し行
|
|
61
68
|
UNION ALL
|
|
62
|
-
SELECT DISTINCT TableId, TableName, 9999 AS ColumnId, '', '', '', '', '', '', '', '' FROM TBLS --空行用
|
|
69
|
+
SELECT DISTINCT TableId, TableName, '', 9999 AS ColumnId, '', '', '', '', '', '', '', '' FROM TBLS --空行用
|
|
63
70
|
)
|
|
64
71
|
SELECT
|
|
65
72
|
CASE WHEN ColumnId = -9 THEN TableName ELSE '' END [テーブル名]
|
|
73
|
+
, CASE WHEN ColumnId = -9 THEN tv ELSE '' END [tv]
|
|
66
74
|
, ColumnName AS [項目名]
|
|
67
75
|
, ColumnDataType AS [データ型]
|
|
68
76
|
, PKey AS [PKEY]
|