-----------------------------------------------------
--
作用：用于
sql server 2005
导出用户表数据字典
--1
默认导出当前数据库中所有用户表
(xtype = 'u')
的数据字典；
--2
可以在
xtype = 'u'
替换为如下所示语句
xtype = 'u' and name in ('son','Person')
导出自定义的表的数据字典；
--3
可以利用集成服务
SSIS
导出数据字典的
excel
文件，形成文档；
------------------------------------------------------
select
sysobjects.name as "
表名
",syscolumns.name as "
字段名
",
case
when systypes.name in ('binary','char','nchar') then (systypes.name + '(' + cast(syscolumns.prec as varchar(50)) + ')')
when
systypes.name in ('decimal','numeric') then (systypes.name + '(' + cast(syscolumns.prec as varchar(50)) + ',' + cast(syscolumns.scaleas varchar(50)) + ')')
when
systypes.name in ('nvarchar','varchar','varbinary') then (case when syscolumns.prec = -1 then (systypes.name + '(MAX)') else(systypes.name + '(' + cast(syscolumns.prec as varchar(50)) + ')') end)
else
systypes.name end as "
字段类型
",
syscomments.text as "
默认值
", case when syscolumns.isnullable = 1 then 'Y' else 'N' end as "
能否为空
",
cast(sys.extended_properties.value as varchar(1024)) as "
备注
"
from
syscolumns inner join systypes on syscolumns.xtype = systypes.xtype left join sysobjects
on
syscolumns.id = sysobjects.id left outer join sys.extended_properties
on
( sys.extended_properties.minor_id = syscolumns.colid and sys.extended_properties.major_id = syscolumns.id) left outer join syscomments
on
syscolumns.cdefault = syscomments.id where syscolumns.id in (select id from sysobjects where xtype = 'u') and (systypes.name <>'sysname') order by sysobjects.name asc
本文转自温景良博客园博客，原文链接：
http://www.cnblogs.com/wenjl520/archive/2008/10/23/1317728.html
，如需转载请自行联系原作者
