MSQL2005以上对象名sysproperties 无效的错误 博客分类： MSSQL SQL Server

在MSSQL2008R2里面执行的时候报错:对象名sysproperties 无效的错误;造成此问题的原因是由于Sql 2005、2008 删除了系统表 sysproperties 而改用 sys.extended_properties 表所致 ， 以下是通过创建sysproperties视图

if exists (select 1 from sysobjects where name = 'sysproperties'and xtype = 'V')begin    
DROP VIEW sysproperties    
end
GO    
CREATE VIEW sysproperties    
AS    
SELECT 
A.name As TableName,   
A.id As ID,B.Name As Name,
B.colid As SMALLID,   
B.xtype As Type,
C.name As PropName,
C.Value As 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)