http://space.itpub.net/16436858/spacelist-blog-itemtypeid-74583 以前用那个SQL语句来生成那个SQLServer库的数据字典，在SQL2000下用的挺好的，最近装上了那个SQL2008来研究， 突然发现不能在2008下用了，查了查资料，发现2008下有一些改动 贴上来留个记
http://space.itpub.net/16436858/spacelist-blog-itemtypeid-74583
以前用那个SQL语句来生成那个SQLServer库的数据字典，在SQL2000下用的挺好的，最近装上了那个SQL2008来研究，
突然发现不能在2008下用了，查了查资料，发现2008下有一些改动
贴上来留个记号
SQLServer2008/2005 生成数据字典语句
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,
标识=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 sys.extended_properties g on a.id=g.major_id and a.colid=g.minor_id
left join sys.extended_properties f on d.id=f.major_id and f.minor_id =0
–where d.name=’要查询的表’ –如果只查询指定表,加上此条件
order by a.id,a.colorder
以前的SQL2000下的语句
就改动了个sysproperties
Code
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,
标识=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 sysproperti本文来源gaodai$ma#com搞$代*码*网(es g on a.id=g.id and a.colid=g.smallid
left join sysproperties f on d.id=f.id and f.smallid=0
–where d.name=’要查询的表’ –如果只查询指定表,加上此条件
order by a.id,a.colorder
转载请注明原文链接：SQLServer2008/2005 生成数据字典SQL语句
