2 hansen chen hansen_chen 于 2014.07.24 16:32 提问

如何解决sql server2008下提示master.dbo.spt_datatype_info不存在的问题?

USE DB_AA
GO
SET ANSI_NULLS, QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE dbo.[GetFldInfo]
WITH ENCRYPTION

AS
Begin
SELECT Cast(Upper(Obj.name) As Varchar(28)) As TABLE_NAME, Cast(Col.name As Varchar(20)) As FieldNAME,
Fld_NO=convert(int,(select count(*) from syscolumns C where C.id = Col.id
AND C.number= Col.number AND C.Colid<= Col.Colid )),
Cast ( spt_dtp.LOCAL_TYPE_NAME As Varchar(20)) As FieldTYPE,
CONVERT(int, OdbcPrec(Col.xtype,Col.length, Col.xprec) + spt_dtp.charbin) As FieldLen,
Cast (Com.text As Varchar(100)) As defaultValue,
CASE Col.isnullable WHEN 1 THEN 'YES' ELSE 'No' END As IsNullable,
ISKey =(select TOP 1 A.ColID from sysIndexkeys A,Sysindexes B where (A.ID=B.ID) and (A.IndID=B.IndID) And (A.ColID=Col.ColID)And (A.ID=Obj.ID) And (B.Name like 'PK%') order by B.INDID )
FROM SysObjects Obj, master.dbo.spt_datatype_info Spt_dtp, Systypes typ, TABLESRC Src ,
Syscolumns Col LEFT OUTER JOIN
Syscomments Com ON Col.cdefault = Com.ID AND Com.ColID = 1
WHERE Obj.ID = Col.ID AND Typ.xtype = Spt_dtp.ss_Dtype AND (spt_dtp.ODBCVer IS NULL OR
Spt_dtp.ODBCVer = 2) AND Obj.xtype='U' And User_ID('dbo')=Obj.UID and
Col.xusertype = Typ.xusertype AND (spt_dtp.AUTO_INCREMENT IS NULL OR
Spt_dtp.AUTO_INCREMENT = 0) And (Upper(Obj.Name)=Src.Table_name)

Order by Src.Table_name,Col.ColID
END
GO
在sql2000下执行没问题,在sql2008下,提示master.dbo.spt_datatype_info不存在

Csdn user default icon
上传中...
上传图片
插入图片
准确详细的回答,更有利于被提问者采纳,从而获得C币。复制、灌水、广告等回答会被删除,是时候展现真正的技术了!