操作步骤如下,供参考。
数据库转型工作涉及的工作事项分析:表,表数据,索引,外键约束,字段默认值。
存储过程、函数、触发器、视图等由于语法存在差异,只能自行改写处理。
(一)在MS SQL SERVER服务器端的准备工作。
1).创建关于表、视图、主键、索引、字段字典、默认值约束的对象视图。以方便下一步使用。
USE EJ_ZSZQ ---USE [数据库名]
GO
--.1不含有TEXT类型、BIGINT类型字段的表视图
CREATE VIEW V_MYTABLE AS
Select UPPER(NAME) AS NAME from sysobjects A
where xtype = 'U' AND NAME<>'dtproperties'
AND NOT EXISTS(SELECT TOP 1 B.NAME FROM SYSCOLUMNS B,SYSTYPES C WHERE B.XTYPE=C.XTYPE AND B.ID=A.ID
AND (C.NAME = 'text' OR C.NAME='bigint'))
GO
--2.含有TEXT类型、BIGINT类型字段的表视图
CREATE VIEW V_MYTABLE2 AS
Select UPPER(NAME) AS NAME from sysobjects A
where xtype = 'U' AND NAME<>'dtproperties'
AND EXISTS(SELECT TOP 1 B.NAME FROM SYSCOLUMNS B,SYSTYPES C WHERE B.XTYPE=C.XTYPE AND B.ID=A.ID
AND (C.NAME = 'text' OR C.NAME='bigint'))
GO
--3.所有主键视图,构建主键时使用
CREATE VIEW V_MYPK AS
SELECT A.parent_obj AS TABLEID,
UPPER(E.NAME) AS TABLENAME,
UPPER(A.NAME) AS INDEXNAME,
UPPER(D.NAME) AS COLNAME,
C.KEYNO AS COLNO,
(SELECT TOP 1 KEYNO
FROM sysindexkeys
WHERE ID = B.ID
AND INDID = B.INDID
ORDER BY KEYNO DESC) AS KEYCNT
FROM sysobjects A,
sysindexes B,
sysindexkeys C,
syscolumns D,
sysobjects E
WHERE (A.xtype = 'PK')
AND (A.parent_obj = B.ID AND A.NAME = B.NAME)
AND (B.ID = C.ID AND B.INDID = C.INDID)
AND (C.ID = D.ID AND C.COLID = D.COLID)
AND (A.parent_obj = E.ID AND E.XTYPE = 'U' AND E.NAME <> 'dtproperties')
GO
--4.所有索引名称及索引字段,不包含主键。构建索引时使用
CREATE VIEW V_MYINDEX AS
SELECT X.*, Y.FIELDCNT
FROM (SELECT A.id as TABLEID,
object_name(A.id) as TABLENAME,
A.name AS INDNAME,
B.INDID,
C.COLID,
C.NAME AS COLNAME,INDEXPROPERTY(A.id,A.name,'IsUnique') as ISUNIQUE
FROM sysindexes A, sysindexkeys B, syscolumns C, sysobjects D
where (A.indid > 0 and A.indid < 255 and (A.status &64) = 0)
AND (A.ID = B.ID AND A.INDID = B.INDID)
AND (B.ID = C.ID AND B.COLID = C.COLID)
AND (C.ID = D.ID AND D.XTYPE = 'U' AND D.PARENT_OBJ = 0 AND
D.NAME <> 'dtproperties')
AND NOT EXISTS (SELECT 1
FROM sysobjects
WHERE XTYPE = 'PK'
AND PARENT_OBJ > 0
AND NAME = A.NAME)) X,
(SELECT ID, INDID, MAX(KEYNO) AS FIELDCNT
FROM sysindexkeys
GROUP BY ID, INDID) Y
WHERE X.tableid = Y.ID
AND X.INDID = Y.INDID
GO
--5字段默认值约束视图
CREATE VIEW V_MYVALUE AS
select OBJECT_NAME(parent_obj) AS TABLENAME,C.COLID,D.NAME AS COLNAME,E.NAME AS DATATYPE, b.TEXT,a.XTYPE
from sysobjects a , syscomments B, sysconstraints C ,SYSCOLUMNS D ,systypes E
where (a.xtype = 'D' AND OBJECTPROPERTY(a.id, N'IsMSShipped') = 0)
AND (A.id = B.id)
AND (A.ID=C.CONSTID AND A.parent_obj=C.ID AND C.status = 2069)
AND (C.ID=D.ID AND C.COLID=D.COLID)
AND (D.XTYPE=E.XTYPE)
--and a.parent_obj = object_id('表名')
GO
--6表字段字典视图
CREATE VIEW V_MYFIELD AS
SELECT B.NAME AS TABLENAME, A.NAME AS COLNAME,A.COLID,C.NAME AS DATATYPE
FROM syscolumns A, SYSOBJECTS B ,SYSTYPES C
WHERE (A.ID=B.ID AND B.XTYPE='U' AND B.NAME <> 'dtproperties')
AND (A.XTYPE=C.XTYPE )
GO
2).创建两个SQL SERVER函数。函数GETSELECTSQL和函数GETORASQL,函数用于输出包含有bigint和text类型字段的表的创建视图的脚本。
USE EJ_ZSZQ
GO
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[GETSELECTSQL](@TABLENAME VARCHAR(30)) RETURNS VARCHAR(2000)
AS
BEGIN
DECLARE @fieldlist VARCHAR(2000)
DECLARE @FIELDNAME VARCHAR(100)
DECLARE @DATATYPE VARCHAR(30)
DECLARE field_cursor CURSOR FOR SELECT COLNAME,DATATYPE FROM V_MYFIELD where TABLENAME = @TABLENAME ORDER BY COLID
SET @fieldlist = ''
OPEN field_cursor
FETCH NEXT FROM field_cursor INTO @FIELDNAME,@DATATYPE
while (@@FETCH_STATUS = 0)
begin
IF @DATATYPE = 'bigint'
begin
SET @FIELDNAME = 'CAST('+@FIELDNAME +' AS NUMERIC(19)) AS ' + @FIELDNAME
end
if @DATATYPE = 'text'
begin
SET @FIELDNAME = 'CAST('+@FIELDNAME +' AS VARCHAR(4000)) AS ' + @FIELDNAME
end
IF @fieldlist = ''
BEGIN