WHEN ''''nvarchar'''' THEN ''''(''''+CONVERT(VARCHAR,B.PREC)+'''')''''

WHEN ''''real'''' THEN ''''''''

WHEN ''''smalldatetime'''' THEN ''''''''

WHEN ''''smallint'''' THEN ''''''''

WHEN ''''smallmoney'''' THEN ''''''''

WHEN ''''sql_variant'''' THEN ''''''''

WHEN ''''sysname'''' THEN ''''''''

WHEN ''''text'''' THEN ''''''''

WHEN ''''timestamp'''' THEN ''''''''

WHEN ''''tinyint'''' THEN ''''''''

WHEN ''''uniqueidentifier'''' THEN ''''''''

WHEN ''''varbinary'''' THEN ''''(''''+CONVERT(VARCHAR,B.PREC)+'''')''''

WHEN ''''varchar'''' THEN ''''(''''+CONVERT(VARCHAR,B.PREC)+'''')''''

WHEN ''''xml'''' THEN ''''''''

ELSE ''''''''END,''''-1'''',''''MAX'''') AS SQLTYPE,

case b.ISNULLABLE

when 0 then ''''NOT NULL''''

ELSE ''''NULL'''' END AS ISNULLABLE,B.COLORDER AS COLUMN_ID

FROM ''''@DBOWNER''''.DBO.sysobjects a

JOIN ''''@DBOWNER''''.DBO.syscolumns b ON a.id=b.id

JOIN ''''@DBOWNER''''.DBO.systypes c ON (b.xtype=c.xtype AND B.XUSERTYPE=C.XUSERTYPE)

WHERE A.XTYPE=''''U'''') A'

--MYSQL结构查询语句

SET @MYSQLTEXT=N'SELECT A.TABLE_NAME AS TABLENAME,B.COLUMN_NAME AS COLUMNNAME,

CASE B.COLUMN_TYPE

WHEN ''''INT(11)'''' THEN ''''INT''''

WHEN ''''TIMESTAMP'''' THEN ''''DATETIME''''

WHEN ''''BLOB'''' THEN ''''VARBINARY(4000)''''

ELSE UPPER(B.COLUMN_TYPE) END AS SQLTYPE,

B.COLUMN_TYPE AS ALLTYPE,

CASE B.IS_NULLABLE

WHEN ''''YES'''' THEN ''''NULL''''

WHEN ''''NO'''' THEN ''''NOT NULL''''

END AS ISNULLABLE,B.ORDINAL_POSITION AS COLUMN_ID

from information_schema.`TABLES` A

JOIN information_schema.`COLUMNS` B

ON (A.TABLE_NAME=B.TABLE_NAME AND A.TABLE_SCHEMA=B.TABLE_SCHEMA AND A.TABLE_CATALOG=B.TABLE_CATALOG)

WHERE A.TABLE_SCHEMA=''''''@DBOWNER'''''''' AND A.TABLE_TYPE=''''BASE TABLE'''''

SELECT @SOURCETEXT=REPLACE(CASE @SOURCETYPE WHEN 1 THEN @ORACLETEXT WHEN 2 THEN @MSSQLTEXT ELSE @MYSQLTEXT END,'''''@DBOWNER''''',@SOURCEDB)

SELECT @TARGETTEXT=REPLACE(CASE @TARGETTYPE WHEN 1 THEN @ORACLETEXT WHEN 2 THEN @MSSQLTEXT ELSE @MYSQLTEXT END,'''''@DBOWNER''''',@TARGETDB)

SELECT @SOURCETEXT= CASE ISNULL(@SOURCELINK,'') WHEN '' THEN '('+REPLACE(@SOURCETEXT,'''''','''')+')' ELSE 'OPENQUERY('+@SOURCELINK+','''+@SOURCETEXT+''')' END

SELECT @TARGETTEXT= CASE ISNULL(@TARGETLINK,'') WHEN '' THEN '('+REPLACE(@TARGETTEXT,'''''','''')+')' ELSE 'OPENQUERY('+@TARGETLINK+','''+@TARGETTEXT+''')' END

----由于远程多表连接查询的开销比较大，因此将查询的结果均放入临时表中

----SET @SQLTEXT=N'SELECT * FROM '+@SOURCETEXT+'A '+CHAR(10)+'JOIN(SELECT * FROM '+@TARGETTEXT+') B '+CHAR(10)+'ON (A.TABLENAME=B.TABLENAME AND A.COLUMNNAME=B.COLUMNNAME)'

IF EXISTS(SELECT * FROM tempdb.dbo.sysobjects WHERE xtype='U' AND name='##TEMPSOURCE')

DROP TABLE ##TEMPSOURCE

IF EXISTS(SELECT * FROM tempdb.dbo.sysobjects WHERE xtype='U' AND name='##TEMPTARGET')

DROP TABLE ##TEMPTARGET

EXEC ('SELECT * INTO ##TEMPSOURCE FROM '+@SOURCETEXT+' A')

EXEC ('SELECT * INTO ##TEMPTARGET FROM '+@TARGETTEXT+' A')