Ispirer Ispirer
 


Ispirer Home Page Ispirer SQLWays Product Page - Migration to SQL Server Request SQLWays

System table – master.dbo.spt_datatype_info

Compatability view should be upgraded to catalog view.

Microsoft SQL Server 2000:

 SELECTFROM master.dbo.spt_datatype_info spt_dtp 
 WHERE (spt_dtp.ODBCVer IS NULL OR spt_dtp.ODBCVer = 2) 

Microsoft SQL Server 2008:

SELECTFROM master.dbo.spt_datatype_info spt_dtp 

For this table also required creation of the additional object:

USE master
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = object_id(N'[dbo].[spt_datatype_info]') AND OBJECTPROPERTY(object_id, N'IsUserTable') = 1)
DROP TABLE [dbo].[spt_datatype_info]
GO
 
CREATE TABLE [dbo].[spt_datatype_info] (
 [TYPE_NAME] [sysname] NOT NULL ,
 [DATA_TYPE] [smallint] NOT NULL ,
 [PRECISION] [int] NULL ,
 [LITERAL_PREFIX] [varchar] (32) COLLATE Japanese_CA_AS NULL ,
 [LITERAL_SUFFIX] [varchar] (32) COLLATE Japanese_CA_AS NULL ,
 [CREATE_PARAMS] [varchar] (32) COLLATE Japanese_CA_AS NULL ,
 [NULLABLE] [smallint] NOT NULL ,
 [CASE_SENSITIVE] [smallint] NOT NULL ,
 [SEARCHABLE] [smallint] NOT NULL ,
 [UNSIGNED_ATTRIBUTE] [smallint] NULL ,
 [MONEY] [smallint] NOT NULL ,
 [AUTO_INCREMENT] [smallint] NULL ,
 [LOCAL_TYPE_NAME] [sysname] COLLATE Japanese_CA_AS NULL ,
 [MINIMUM_SCALE] [smallint] NULL ,
 [MAXIMUM_SCALE] [smallint] NULL ,
 [SQL_DATA_TYPE] [smallint] NOT NULL ,
 [SQL_DATETIME_SUB] [smallint] NULL ,
 [RADIX] [smallint] NULL ,
 [INTERVAL_PRECISION] [smallint] NULL ,
 [USERTYPE] [smallint] NULL
)
GO
INSERT [dbo].[spt_datatype_info] EXEC sp_datatype_info
GO
 
ALTER TABLE [dbo].[spt_datatype_info] ADD [ss_dtype] [smallint] NULL
GO
 
UPDATE [dbo].[spt_datatype_info] SET [ss_dtype] = user_type_id FROM sys.types WHERE [name] = CASE
 WHEN charindex('(', [TYPE_NAME]) > 0 
  THEN LEFT([TYPE_NAME], charindex('(', [TYPE_NAME])-1)
 WHEN charindex(' ', [TYPE_NAME]) > 0 
  THEN LEFT([TYPE_NAME], charindex(' ', [TYPE_NAME])-1)
 ELSE [TYPE_NAME] END
GO
 
ALTER TABLE [dbo].[spt_datatype_info] ADD [charbin] [smallint] NULL
GO
 
UPDATE [dbo].[spt_datatype_info] SET [charbin] = 0
  WHERE lower([TYPE_NAME]) IN ('char', 'varchar', 'text', 'ntext', 'nvarchar', 'nchar', 'binary', 'varbinary', 'sql_variant', 'image', 'timestamp')
GO

Column conversion:

Microsoft SQL Server 2000 (master.dbo.spt_datatype_info) Microsoft SQL Server 2008 (master.dbo.spt_datatype_info)
LOCAL_TYPE_NAME LOCAL_TYPE_NAME
charbin charbin
RADIX RADIX
SQL_DATETIME_SUB SQL_DATETIME_SUB
ss_dtype ss_dtype
AUTO_INCREMENT AUTO_INCREMENT

Ispirer Home Page Ispirer SQLWays Product Page - Migration to SQL Server Request SQLWays

sqlways/sql-server/upgrade/systemobjects/spt_datatype_info.txt · Last modified: May 07, 2018, 03:15:34 PM (external edit)
 
© 1999-2019, Ispirer Systems Ltd.
All Rights Reserved.  Privacy Statement