以下は私がテーブル構造を知りたい時にべろっと投げるSQLです。
(SQLServer 2005, 2014 では動いてます)
use <DBName> とかして、以下をそのままコピペすれば動くと思います。
sql
1 WITH TBLS AS (
2 SELECT
3 tbl . object_id AS TableId
4 , tbl . name AS TableName
5 , tbl . tv
6 , col . column_id AS ColumnId
7 , col . name AS ColumnName
8
9 -- データ型/長さ
10 , SUBSTRING (
11 dtp . name
12 + CASE WHEN dtp . name IN ( 'datetime' , 'int' , 'bit' , 'decimal' , 'bigint' , 'tinyint' , 'money' , 'text' , 'ntext' ) THEN ''
13 ELSE '(' +
14 CASE WHEN dtp . name IN ( 'varchar' , 'nvarchar' , 'varbinary' )
15 AND col . max_length = - 1 THEN 'max'
16 WHEN dtp . name IN ( 'nchar' , 'nvarchar' ) THEN
17 CONVERT ( varchar , ( col . max_length / 2 ) )
18 ELSE CONVERT ( varchar , col . max_length ) END
19 + ')'
20 END
21 , 1 , 20 ) AS ColumnDataType
22
23 -- PKEY
24 , CASE WHEN
25 ( SELECT TOP 1 1 FROM information_schema . key_column_usage x
26 WHERE x . table_name = tbl . name AND x . column_name = col . name )
27 = 1 THEN '○' ELSE '' END AS PKey
28
29 -- NULL許可
30 , CASE WHEN col . is_nullable = 1 THEN '' ELSE '○' END AS NotNull
31
32 -- IDENTITY
33 , CASE WHEN idt . object_id IS NULL THEN ''
34 ELSE 'IDENTITY(' + CONVERT ( varchar , idt . seed_value ) + ', '
35 + CONVERT ( varchar , idt . increment_value ) + ')' END AS ColumnIdentity
36 -- COLLATE
37 , CASE WHEN col . collation_name IS NULL THEN ''
38 ELSE 'COLLATE ' + col . collation_name END AS Collation
39 -- コメント
40 , ep . value AS ColumnComment
41 , tep . value AS TableComment
42
43 --FROM sys.tables tbl
44 FROM (
45 SELECT 'T' AS [ tv ] , object_id , name FROM sys . tables UNION ALL
46 SELECT 'V' AS [ tv ] , object_id , name FROM sys . views
47 ) tbl
48
49 LEFT JOIN sys . columns col ON ( col . object_id = tbl . object_id )
50 LEFT JOIN sys . types dtp ON ( dtp . system_type_id = col . system_type_id AND dtp . user_type_id = col . user_type_id )
51 LEFT JOIN sys . identity_columns idt ON ( idt . object_id = col . object_id AND idt . column_id = col . column_id )
52 LEFT JOIN sys . extended_properties ep ON ( ep . major_id = col . object_id AND ep . minor_id = col . column_id AND ep . name = 'MS_Description' )
53 LEFT JOIN sys . extended_properties tep ON ( tep . major_id = tbl . object_id AND tep . minor_id = 0 AND tep . name = 'MS_Description' )
54 -- ORDER BY tbl.name, col.column_id
55 )
56 , DOCS AS (
57 SELECT * FROM TBLS
58 UNION ALL
59 SELECT DISTINCT TableId , TableName , tv , - 9 AS ColumnId , '' , '' , '' , '' , '' , '' , '' , TableComment FROM TBLS --テーブル名行
60 UNION ALL
61 SELECT DISTINCT TableId , TableName , tv , - 8 AS ColumnId , '項目名' , 'データ型' , 'PKEY' , 'NOT NULL' , 'IDENTITY' , '照合順序' , 'コメント' , TableComment FROM TBLS --見出し行
62 UNION ALL
63 SELECT DISTINCT TableId , TableName , tv , 9999 AS ColumnId , '' , '' , '' , '' , '' , '' , '' , '' FROM TBLS --空行用
64 )
65 SELECT
66 CASE WHEN ColumnId = - 9 THEN TableName ELSE '' END [ テーブル名 ]
67 , CASE WHEN ColumnId = - 9 THEN tv ELSE '' END [ tv ]
68 , ColumnName AS [ 項目名 ]
69 , ColumnDataType AS [ データ型 ]
70 , PKey AS [ PKEY ]
71 , NotNull AS [ NOT NULL ]
72 , ColumnIdentity AS [ IDENTITY ]
73 , Collation AS [ 照合順序 ]
74 , CASE WHEN ColumnId BETWEEN 1 AND 9998 THEN
75 CASE WHEN ColumnIdentity <> '' THEN ColumnIdentity + '; ' ELSE '' END
76 + CONVERT ( varchar , ISNULL ( ColumnComment , '' ) )
77 ELSE ColumnComment END AS [ コメント ]
78 FROM DOCS
79 WHERE TableName LIKE '%%' -- ★★★テーブル名
80 ORDER BY TableName , ColumnId
私も結構前に色々ぐぐって見つけたSQLを切り貼りして作ったものなので
もしどこか間違えていたらごめんなさい…
これは私が見やすいように色々整形していますが、
必要な情報的には最初の WITH 句 <TBLS> の SELECT だけで事足りる筈です。
バッドをするには、ログインかつ
こちらの条件を満たす必要があります。
2016/09/14 09:07 編集
2016/09/14 09:01
2016/09/14 14:13