--生成整个数据库全部表的数据字典:
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 '表名'