mssql sqlserver 2008 错误信息提示“对象名 ‘dbo.sysproperties’ 无效’”


摘要:
今天使用以前在sqlserver 2000生成数据字典的脚本放入sqlserver 2008数据库中,提示如下错误信息,
“对象名 ‘dbo.sysproperties’ 无效”,下文将讲述在sqlserver 2008中 对象名无效的处理方法


在sqlserver 2005 2008及以后的版本中,sqlserver将 取消了系统视图”sysproperties”,我们可以通过
“sys.extended_properties”扩展视图生成 “sysproperties” 视图代替sqlserver 2000中的系统视图,如下所示:

 if exists (select 1 from sysobjects where name = 'sysproperties'and xtype = 'v')
 begin
    create view sysproperties
    as
    select a.name as objectName,
    a.id as parentId,b.name,b.colid,
    b.xtype,c.name as propname,c.value
    from sysobjects as a inner join syscolumns as b on a.id = b.id
    inner join sys.extended_properties as c on c.major_id = a.id 
    and ( minor_id = b.colid)
 end