--生成整个数据库全部表的数据字典：

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 '表名'