欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

SQLServer2008/2005 生成数据字典SQL语句

程序员文章站 2024-01-17 18:43:04
...

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 生成数据字典语句

SQLServer2008/2005 生成数据字典SQL语句SELECT

SQLServer2008/2005 生成数据字典SQL语句 表名=case when a.colorder=1 then d.name else '' end,

SQLServer2008/2005 生成数据字典SQL语句 表说明=case when a.colorder=1 then isnull(f.value,'') else '' end,

SQLServer2008/2005 生成数据字典SQL语句 字段序号=a.colorder,

SQLServer2008/2005 生成数据字典SQL语句 字段名=a.name,

SQLServer2008/2005 生成数据字典SQL语句 标识=case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end,

SQLServer2008/2005 生成数据字典SQL语句 主键=case when exists(SELECT 1 FROM sysobjects where xtype='PK' and name in (

SQLServer2008/2005 生成数据字典SQL语句 SELECT name FROM sysindexes WHERE indid in(

SQLServer2008/2005 生成数据字典SQL语句 SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid

SQLServer2008/2005 生成数据字典SQL语句 ))) then '√' else '' end,

SQLServer2008/2005 生成数据字典SQL语句 类型=b.name,

SQLServer2008/2005 生成数据字典SQL语句 占用字节数=a.length,

SQLServer2008/2005 生成数据字典SQL语句 长度=COLUMNPROPERTY(a.id,a.name,'PRECISION'),

SQLServer2008/2005 生成数据字典SQL语句 小数位数=isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),

SQLServer2008/2005 生成数据字典SQL语句 允许空=case when a.isnullable=1 then '√'else '' end,

SQLServer2008/2005 生成数据字典SQL语句 默认值=isnull(e.text,''),

SQLServer2008/2005 生成数据字典SQL语句 字段说明=isnull(g.[value],'')

SQLServer2008/2005 生成数据字典SQL语句 FROM syscolumns a

SQLServer2008/2005 生成数据字典SQL语句 left join systypes b on a.xtype=b.xusertype

SQLServer2008/2005 生成数据字典SQL语句 inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name'dtproperties'

SQLServer2008/2005 生成数据字典SQL语句 left join syscomments e on a.cdefault=e.id

SQLServer2008/2005 生成数据字典SQL语句 left join sys.extended_properties g on a.id=g.major_id and a.colid=g.minor_id

SQLServer2008/2005 生成数据字典SQL语句 left join sys.extended_properties f on d.id=f.major_id and f.minor_id =0

SQLServer2008/2005 生成数据字典SQL语句 --where d.name='要查询的表' --如果只查询指定表,加上此条件

SQLServer2008/2005 生成数据字典SQL语句 order by a.id,a.colorder

SQLServer2008/2005 生成数据字典SQL语句

以前的SQL2000下的语句

就改动了个sysproperties

SQLServer2008/2005 生成数据字典SQL语句SQLServer2008/2005 生成数据字典SQL语句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 sysproperties 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