sql2000导出数据字典表（含字段名、格式、长度、备注等），可保存到excel

--生成整个数据库全部表的数据字典：
SELECT   
    表名 = (case when a.colorder = 1 then d.name else '' end),
    字段序号 = a.colorder,
    字段名 = a.name,
    标识 = (case when columnproperty( a.id,a.name,'isidentity')=1 then '√'else '' end),
    主键 = (case when exists(select 1 from sysobjects where xtype='pk' 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,''),
    字段说明 = isnull(g.[value],'')
FROM     syscolumns a
    LEFT JOIN systypes b ON a.xtype = 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 sysproperties g ON a.id = g.id AND a.colid = g.smallid 
ORDER BY a.id, a.colorder

--生成特定表的结构
SELECT   
    表名 = (case when a.colorder = 1 then d.name else '' end),
    字段序号 = a.colorder,
    字段名 = a.name,
    标识 = (case when columnproperty( a.id,a.name,'isidentity')=1 then '√'else '' end),
    主键 = (case when exists(select 1 from sysobjects where xtype='pk' 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,''),
    字段说明 = isnull(g.[value],'')
FROM     syscolumns a
    LEFT JOIN systypes b ON a.xtype = 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 sysproperties g ON a.id = g.id AND a.colid = g.smallid 
WHERE d.name = '表名' --表名
ORDER BY a.id, a.colorder

--表信息、表结构
exec sp_help '表名'