1、生成数据表文档
可以使用sp_MShelpcolumns,例如:
exec
sp_MShelpcolumns N
'
[dbo].[fy]
'
,
@orderby
=
'
id
'
2、生成存储过程、自定义函数和视图的文档
select
a.id,a.
text
,b.name,b.xtype
from
dbo.syscomments a
join
sysobjects b
on
a.id
=
b.id
where b.xtype in ( ' p ' , ' v ' , ' FN ' ) and b.status > 0
where b.xtype in ( ' p ' , ' v ' , ' FN ' ) and b.status > 0
3、生成数据表文档的SQL语句
1
SELECT
(
CASE
WHEN
a.colorder
=
1
THEN
d.name
ELSE
''
END
) N
'
TableName
'
,
2 a.colorder N ' SortID ' , a.name N ' ColumnName ' ,
3 ( CASE WHEN COLUMNPROPERTY (a.id,a.name, ' IsIdentity ' ) = 1 THEN ' √ ' ELSE '' END ) N ' IsIdentity ' ,
4 ( CASE WHEN (
5 SELECT COUNT ( * ) FROM sysobjects
6 WHERE (name IN (
7 SELECT name FROM sysindexes
8 WHERE (id = a.id)
9 AND (indid IN (
10 SELECT indid
11 FROM sysindexkeys
12 WHERE (id = a.id)
13 AND (colid IN (
14 SELECT colid FROM syscolumns
15 WHERE (id = a.id)
16 AND (name = a.name
17 )
18 )
19 )
20 )
21 )
22 )
23 )
24 AND (xtype = ' PK ' )) = 0
25 THEN '' ELSE ' √ ' END ) N ' IsKey ' ,
26 b.name N ' ColType ' ,
27 a.length N ' Bits ' ,
28 COLUMNPROPERTY (a.id, a.name, ' PRECISION ' ) AS N ' Length ' ,
29 IsNull ( COLUMNPROPERTY (a.id, a.name, ' Scale ' ), 0 ) AS N ' Scale ' ,
30 ( CASE WHEN a.isnullable = 1 THEN ' √ ' ELSE '' END ) N ' IsNullAble ' ,
31 isnull (e. text , '' ) N ' Default ' ,
32 isnull (g. [ value ] , '' ) AS N ' Description '
33
34 FROM syscolumns a LEFT JOIN
35 systypes b ON a.xtype = b.xusertype INNER JOIN
36 sysobjects d ON a.id = d .id AND d .xtype = ' U ' LEFT JOIN
37 syscomments e ON a.cdefault = e.id LEFT JOIN
38 sysproperties g ON a.id = g.id AND a.colid = g.smallid
39 ORDER BY object_name (a.id), a.colorder
40
41
2 a.colorder N ' SortID ' , a.name N ' ColumnName ' ,
3 ( CASE WHEN COLUMNPROPERTY (a.id,a.name, ' IsIdentity ' ) = 1 THEN ' √ ' ELSE '' END ) N ' IsIdentity ' ,
4 ( CASE WHEN (
5 SELECT COUNT ( * ) FROM sysobjects
6 WHERE (name IN (
7 SELECT name FROM sysindexes
8 WHERE (id = a.id)
9 AND (indid IN (
10 SELECT indid
11 FROM sysindexkeys
12 WHERE (id = a.id)
13 AND (colid IN (
14 SELECT colid FROM syscolumns
15 WHERE (id = a.id)
16 AND (name = a.name
17 )
18 )
19 )
20 )
21 )
22 )
23 )
24 AND (xtype = ' PK ' )) = 0
25 THEN '' ELSE ' √ ' END ) N ' IsKey ' ,
26 b.name N ' ColType ' ,
27 a.length N ' Bits ' ,
28 COLUMNPROPERTY (a.id, a.name, ' PRECISION ' ) AS N ' Length ' ,
29 IsNull ( COLUMNPROPERTY (a.id, a.name, ' Scale ' ), 0 ) AS N ' Scale ' ,
30 ( CASE WHEN a.isnullable = 1 THEN ' √ ' ELSE '' END ) N ' IsNullAble ' ,
31 isnull (e. text , '' ) N ' Default ' ,
32 isnull (g. [ value ] , '' ) AS N ' Description '
33
34 FROM syscolumns a LEFT JOIN
35 systypes b ON a.xtype = b.xusertype INNER JOIN
36 sysobjects d ON a.id = d .id AND d .xtype = ' U ' LEFT JOIN
37 syscomments e ON a.cdefault = e.id LEFT JOIN
38 sysproperties g ON a.id = g.id AND a.colid = g.smallid
39 ORDER BY object_name (a.id), a.colorder
40
41