hansen_chen 2014-07-24 08:32
浏览 1305

如何解决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不存在

  • 写回答

0条回答

    报告相同问题?

    悬赏问题

    • ¥20 腾讯企业邮箱邮件可以恢复么
    • ¥15 有人知道怎么将自己的迁移策略布到edgecloudsim上使用吗?
    • ¥15 错误 LNK2001 无法解析的外部符号
    • ¥50 安装pyaudiokits失败
    • ¥15 计组这些题应该咋做呀
    • ¥60 更换迈创SOL6M4AE卡的时候,驱动要重新装才能使用,怎么解决?
    • ¥15 让node服务器有自动加载文件的功能
    • ¥15 jmeter脚本回放有的是对的有的是错的
    • ¥15 r语言蛋白组学相关问题
    • ¥15 Python时间序列如何拟合疏系数模型