领导安排要整理公司一个大型项目的数据库,要求最终形成一个表结构文档,方便以后维护使用,并让在整理SQL过程了解项目。
搜索后找到一个SQL脚本,修改后执行下SQL脚本。
在结果就能看到数据库所有表的结构,点击右上角全选然后右键,选择将结果另存为CSV,然后在复制到Excel里面然后就可以方便查看。
另外的参考 :
http://www.cnblogs.com/downmoon/archive/2012/05/04/2482995.html
http://www.cnblogs.com/LeeYongze/archive/2012/07/19/2599338.html
SQL Server脚本:
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 sys.syscolumns AS a LEFT OUTER JOIN
sys.systypes AS b ON a.xusertype = b.xusertype INNER JOIN
sys.sysobjects AS d ON a.id = d.id AND d.xtype = 'U' AND d.name <> 'dtproperties' LEFT OUTER JOIN
sys.syscomments AS e ON a.cdefault = e.id LEFT OUTER JOIN
sys.extended_properties AS g ON a.id = g.major_id AND a.colid = g.minor_id LEFT OUTER JOIN
sys.extended_properties AS f ON d.id = f.major_id AND f.minor_id = 0
where d.name='alldocument' --如果只查询指定表,加上此条件
//where d.name='alldocument' --查询全部表
ORDER BY a.id, 字段序号
//ORDER BY d.name asc , a.id, 字段序号 -- d.name asc 按照字母排序表
效果图如下: