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

http://space.itpub.net/16436858/spacelist-blog-itemtypeid-74583

以前用那个SQL语句来生成那个SQLServer库的数据字典，在SQL2000下用的挺好的，最近装上了那个SQL2008来研究，

突然发现不能在2008下用了，查了查资料，发现2008下有一些改动

贴上来留个记号

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

??

?

?

以前的SQL2000下的语句

就改动了个sysproperties

?

SELECT?????

??表名=case???when???a.colorder=1???then???d.name???else???''???end,???

??表说明=case???when???a.colorder=1???then???isnull(f.value,???

??字段序号=a.colorder,???

??字段名=a.name,???

??标识=case???when???COLUMNPROPERTY(???a.id,???

??主键=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,???

??小数位数=isnull(COLUMNPROPERTY(a.id,???

??允许空=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???

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!