DECLARE @TABLE_NAME NVARCHAR(200)
DECLARE @SCHEMA NVARCHAR(20)
--传入表名、架构名
SET @TABLE_NAME='TableA'
SET @SCHEMA='dbo'
SELECT
'OR ISNULL(TARGET.' + A.NAME + ','+
(CASE WHEN B.NAME ='CHAR' OR B.NAME ='NVARCHAR' OR B.NAME ='VARCHAR' OR B.NAME ='NVARCHAR' THEN '''''' WHEN B.NAME ='DATETIME' OR B.NAME ='DATE' OR B.NAME ='DATETIME2' THEN '''1900-01-01'''
WHEN B.NAME in('INT','SMALLINT','BIGINT','FLOAT','NUMERIC','DECIMAL') and a.xprec=1 and a.xscale=0 THEN '-9'
WHEN B.NAME in('INT','SMALLINT','BIGINT','FLOAT','NUMERIC','DECIMAL') and a.xprec=2 and a.xscale=0 THEN '-99'
WHEN B.NAME in('INT','SMALLINT','BIGINT','FLOAT','NUMERIC','DECIMAL') and a.xprec=3 and a.xscale=0 THEN '-999'
WHEN B.NAME in('INT','SMALLINT','BIGINT','FLOAT','NUMERIC','DECIMAL') and a.xprec>=4 and a.xscale=0 THEN '-9999'
WHEN (B.NAME ='FLOAT' OR B.NAME ='NUMERIC' OR B.NAME = 'DECIMAL') and (a.xprec-a.xscale)=1 and a.xscale<>0 THEN '-9.9'
WHEN (B.NAME ='FLOAT' OR B.NAME ='NUMERIC' OR B.NAME = 'DECIMAL') and (a.xprec-a.xscale)=2 and a.xscale<>0 THEN '-99.9'
WHEN (B.NAME ='FLOAT' OR B.NAME ='NUMERIC' OR B.NAME = 'DECIMAL') and (a.xprec-a.xscale)=3 and a.xscale<>0 THEN '-999.9'
WHEN (B.NAME ='FLOAT' OR B.NAME ='NUMERIC' OR B.NAME = 'DECIMAL') and (a.xprec-a.xscale)>=4 and a.xscale<>0 THEN '-9999.9'
WHEN B.NAME = 'VARBINARY' THEN 'CONVERT(BINARY,'''')' ELSE 'N''未知''' END)+')
<> ISNULL(SOURCE.' + A.NAME + ',' +
(CASE WHEN B.NAME ='CHAR' OR B.NAME ='NVARCHAR' OR B.NAME ='VARCHAR' OR B.NAME ='NVARCHAR' THEN '''''' WHEN B.NAME ='DATETIME' OR B.NAME ='DATE' OR B.NAME ='DATETIME2' THEN '''1900-01-01'''
WHEN B.NAME in('INT','SMALLINT','BIGINT','FLOAT','NUMERIC','DECIMAL') and a.xprec=1 and a.xscale=0 THEN '-9'
WHEN B.NAME in('INT','SMALLINT','BIGINT','FLOAT','NUMERIC','DECIMAL') and a.xprec=2 and a.xscale=0 THEN '-99'
WHEN B.NAME in('INT','SMALLINT','BIGINT','FLOAT','NUMERIC','DECIMAL') and a.xprec=3 and a.xscale=0 THEN '-999'
WHEN B.NAME in('INT','SMALLINT','BIGINT','FLOAT','NUMERIC','DECIMAL') and a.xprec>=4 and a.xscale=0 THEN '-9999'
WHEN (B.NAME ='FLOAT' OR B.NAME ='NUMERIC' OR B.NAME = 'DECIMAL') and (a.xprec-a.xscale)=1 and a.xscale<>0 THEN '-9.9'
WHEN (B.NAME ='FLOAT' OR B.NAME ='NUMERIC' OR B.NAME = 'DECIMAL') and (a.xprec-a.xscale)=2 and a.xscale<>0 THEN '-99.9'
WHEN (B.NAME ='FLOAT' OR B.NAME ='NUMERIC' OR B.NAME = 'DECIMAL') and (a.xprec-a.xscale)=3 and a.xscale<>0 THEN '-999.9'
WHEN (B.NAME ='FLOAT' OR B.NAME ='NUMERIC' OR B.NAME = 'DECIMAL') and (a.xprec-a.xscale)>=4 and a.xscale<>0 THEN '-9999.9'
WHEN B.NAME = 'VARBINARY' THEN 'CONVERT(BINARY,'''')' ELSE 'N''未知''' END)+')'
FROM SYSCOLUMNS A
LEFT JOIN SYSTYPES B ON A.XUSERTYPE=B.XUSERTYPE
INNER JOIN SYSOBJECTS D
ON A.ID=D.ID AND D.XTYPE='U' AND D.NAME<>'DTPROPERTIES'
LEFT JOIN SYS.OBJECTS F
ON F.OBJECT_ID = D.ID
LEFT JOIN SYS.SCHEMAS E
ON F.SCHEMA_ID=E.SCHEMA_ID
WHERE D.NAME =@TABLENAME AND E.NAME =@SCHEMA
SQL字段比较的自动化生成脚本
最新推荐文章于 2022-05-16 19:57:45 发布