SQL server 2000与2005中一些系统表的变化

sunwy

select   a.name,rtrim(b.name)   as   colname     

,case   when   h.id   is   not   null   then   'PK'   else   ''   end   as   primarykey     ,type_name(b.xusertype)   +   case   when   b.colstat   &   1   =   1   then   '[ID('   +   convert(varchar,ident_seed(a.name))   +   ','   +   convert(varchar,ident_incr(a.name))   +   ')]'   else   ''   end   as   type   

,b.length     

,case   b.isnullable   when   0   then   'N'   else   'Y'   end   as   [isnull]     ,isnull(e.text,'')   as   [default]     ,isnull(c.value,'')   as   descript      

from   sysobjects   a,syscolumns   b      

left   outer   join   sysproperties   c   on   b.id   =   c.id   and   b.colid   =   c.smallid      

left   outer   join   syscomments   e   on   b.cdefault   =   e.id     

left   outer   join   (select   g.id,g.colid   from   sysindexes   f,sysindexkeys   g   where   f.id   =   g.id   and   f.indid   =   g.indid   and   f.indid   >   0   and   f.indid   <   255   and   (f.status   &   2048)<>0)   h   

on   b.id   =   h.id   and   b.colid   =   h.colid     

where   a.id   =   b.id   and   a.status>0   and   h.id   is   not   null     

order   by   b.colid  

使用上面的语句可以在SQL server 2000中查出当前database中的所有主键的信息。如查询系统数据库master可以获得如下信息。  name
colname  primarykey type length isnull default descript      
PRODUCT ID PK int 4 N     
SEVERITY ID PK int 4 N     
  上面的语句在SQL server 2005中是不好用的，因为SQL2000中的表sysproperties在SQL2005中已经不存在了，取而代之的是表sys.extended_properties，并且两个标的结构也不同。  这是表sysproperties的结构定义。 Column name
Data type length
id int 4
smallid smallint 2
type tinyint 1
name sysname 128
[value] sql_variant   
  这是表sys.extended_properties的结构定义（可以查阅MSDN获得详细的信息）。 Column name
Data type  
class tinyint
class_desc nvarchar(60)
major_id int
minor_id int
name sysname
value sql_variant

   所以上面的代码需要修改为如下代码才能在SQL2005中执行，可是查询的结果却是0条记录，这是为什么呢：  

select   a.name,rtrim(b.name)   as   colname   

,case   when   h.id   is   not   null   then   'PK'   else   ''   end   as   primarykey     ,type_name(b.xusertype)   +   case   when   b.colstat   &   1   =   1   then   '[ID('   +   convert(varchar,ident_seed(a.name))   +   ','   +   convert(varchar,ident_incr(a.name))   +   ')]'   else   ''   end   as   type     

,b.length     

,case   b.isnullable   when   0   then   'N'   else   'Y'   end   as   [isnull]     ,isnull(e.text,'')   as   [default]     ,isnull(c.value,'')   as   descript       from   sysobjects   a,syscolumns   b      

left   outer   join   sys.extended_properties   c   on   b.id   =   c.major_id   and   b.colid   =   c.major_id      

left   outer   join   syscomments   e   on   b.cdefault   =   e.id     

left   outer   join   (select   g.id,g.colid   from   sysindexes   f,sysindexkeys   g   where   f.id   =   g.id   and   f.indid   =   g.indid   and   f.indid   >   0   and   f.indid   <   255   and   (f.status   &   2048)<>0)   h   

on   b.id   =   h.id   and   b.colid   =   h.colid   

where   a.id   =   b.id   and   a.status>0   and   h.id   is   not   null     

order   by   b.colid  

经过一顿排查，终于找到问题所在，问题就出在后面的a.status>0   一句。因为表sysobjects在SQL2000和SQL2005中对于status这一字段的赋值有所不同：可以看到2000的表sysobjects的status值有小于0的，有大于0的，也有等于0的，可是在2005中表sysobjects的status值全部等于0 。因此只要将a.status>0改为a.status>=0即可正确执行。如果修改后的语句仍不能正确执行，可以尝试变换sys.extended_properties 的major_id与minor_id（MSDN察看详细信息）。