导出数据库设计文档

1
2
3
4
5
6
7
8
9
10
SELECT

COLUMN_NAME名称，

COLUMN_TYPE类型,

COLUMN_COMMENT备注，

COLUMN_KEY 索引

FROM

information_schema.COLUMNS

WHERE

TABLE_SCHEMA='db_rcs'

AND table_name ='表名'

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
SELECT DISTINCT

atc.table_name AS "表名"

,atcom.comments AS "表注释"

,atc.column_id AS "字段序号"

,atc.column_name AS "字段名"

,atc.data_type AS "字段类型"

,atc.data_scale AS "字段精度"

,atc.data_length AS "字段长度"

,acc.comments AS "字段注释"

,allt.num_rows as "表数据量"

FROM

all_tab_columns atc

INNER join all_col_comments acc ON atc.table_name = acc.table_name AND atc.owner = acc.owner AND atc.column_name = acc.column_name

INNER join all_tab_comments atcom ON atc.table_name = atcom.table_name AND atcom.owner = acc.owner AND atcom.table_type = 'TABLE'

INNER join all_tables allt on atc.table_name = allt.table_name AND atc.owner = allt.owner

WHERE

atc.owner = 'PAN' and atc.table_name like 'KF_%'

ORDER BY

atc.table_name,

atc.column_id;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
SELECT

表名       = Case When A.colorder=1 Then D.name Else '' End,

表说明     = Case When A.colorder=1 Then isnull(F.value,'') Else '' End,

字段序号   = A.colorder,

字段名     = A.name,

字段说明   = isnull(G.[value],''),

标识       = Case When COLUMNPROPERTY( A.id,A.name,'IsIdentity')=1 Then '√'Else '' End,

主键       = Case When exists(SELECT 1 FROM sysobjects Where xtype='PK' and parent_obj=A.id and name in (

  SELECT name FROM sysindexes WHERE indid in( SELECT indid FROM sysindexkeys WHERE id = A.id AND colid=A.colid))) then '√' else '' end,

  类型       = B.name,

  占用字节数 = A.Length,

  长度       = COLUMNPROPERTY(A.id,A.name,'PRECISION'),

  小数位数   = isnull(COLUMNPROPERTY(A.id,A.name,'Scale'),0),

  允许空     = Case When A.isnullable=1 Then '√'Else '' End,

  默认值     = isnull(E.Text,'')

  FROM

  syscolumns A

  Left Join

  systypes B

  On

  A.xusertype=B.xusertype

  Inner Join

  sysobjects D

  On

  A.id=D.id  and D.xtype='U' and  D.name<>'dtproperties'

  Left Join

  syscomments E

  on

  A.cdefault=E.id

  Left Join

  sys.extended_properties  G

  on

  A.id=G.major_id and A.colid=G.minor_id

  Left Join

  

  sys.extended_properties F

  On

  D.id=F.major_id and F.minor_id=0

  --where d.name='HOSAPI_USER'    --如果只查询指定表,加上此条件

  Order By

     A.id,A.colorder