查询新数据库与原数据库列名是否相同

最新推荐文章于 2024-10-11 17:04:33 发布

zhenming39

最新推荐文章于 2024-10-11 17:04:33 发布

阅读量298

点赞数 2

文章标签：数据库 join null c

本文链接：https://blog.csdn.net/zhenming39/article/details/6087411

版权

DECLARE @ODB_NAME NVARCHAR(100),@SQLSTRING NVARCHAR(4000)
SET @ODB_NAME ='EDB_NAME'
SET @SQLSTRING='DECLARE @ODB_NAME NVARCHAR(100),@ODIS_COUNT INT,@NDIS_COUNT INT '
SET @SQLSTRING=@SQLSTRING+'SELECT @ODIS_COUNT=COUNT(NAME) FROM [@]..SYSOBJECTS WHERE TYPE=''U'' '
SET @SQLSTRING=@SQLSTRING+'AND NAME NOT IN (SELECT NAME FROM SYSOBJECTS WHERE TYPE=''U'') '
SET @SQLSTRING=@SQLSTRING+'SELECT @NDIS_COUNT=COUNT(NAME) FROM SYSOBJECTS WHERE TYPE=''U'' '
SET @SQLSTRING=@SQLSTRING+'AND NAME NOT IN (SELECT NAME FROM [@]..SYSOBJECTS WHERE TYPE=''U'') '
SET @SQLSTRING=@SQLSTRING+'IF((@ODIS_COUNT+@NDIS_COUNT)=0) '
SET @SQLSTRING=@SQLSTRING+'BEGIN '
SET @SQLSTRING=@SQLSTRING+'PRINT ''表名是否对应：是'' '
SET @SQLSTRING=@SQLSTRING+'SELECT @ODIS_COUNT=COUNT(C.ID) FROM [@]..SYSCOLUMNS AS C LEFT JOIN [@]..SYSOBJECTS AS O ON O.ID=C.ID '
SET @SQLSTRING=@SQLSTRING+'LEFT JOIN SYSOBJECTS AS LO ON LO.NAME=O.NAME '
SET @SQLSTRING=@SQLSTRING+'LEFT JOIN SYSCOLUMNS AS LC ON LC.ID=LO.ID AND LC.NAME=C.NAME WHERE O.TYPE=''U'' AND LC.NAME IS NULL '
SET @SQLSTRING=@SQLSTRING+'SELECT @NDIS_COUNT=COUNT(C.ID) FROM SYSCOLUMNS AS C LEFT JOIN SYSOBJECTS AS O ON O.ID=C.ID '
SET @SQLSTRING=@SQLSTRING+'LEFT JOIN [@]..SYSOBJECTS AS LO ON LO.NAME=O.NAME '
SET @SQLSTRING=@SQLSTRING+'LEFT JOIN [@]..SYSCOLUMNS AS LC ON LC.ID=LO.ID AND LC.NAME=C.NAME WHERE O.TYPE=''U'' AND LC.NAME IS NULL '
SET @SQLSTRING=@SQLSTRING+'IF((@ODIS_COUNT+@NDIS_COUNT)=0) '
SET @SQLSTRING=@SQLSTRING+'PRINT ''列名是否对应：是'' '
SET @SQLSTRING=@SQLSTRING+'ELSE '
SET @SQLSTRING=@SQLSTRING+'BEGIN '
SET @SQLSTRING=@SQLSTRING+'PRINT ''列名是否对应：否'' '
SET @SQLSTRING=@SQLSTRING+'PRINT ''列名差异信息如下：原 [''+CONVERT(NVARCHAR(100),@ODIS_COUNT)+'']''+'' '
SET @SQLSTRING=@SQLSTRING+'现 [''+CONVERT(NVARCHAR(100),@NDIS_COUNT)+'']'' '
SET @SQLSTRING=@SQLSTRING+'SELECT ''原【 ''+C.NAME+'' 】'' AS ''差异列名'' ,''原【 ''+O.NAME+'' 】'' AS ''该列所在表'' '
SET @SQLSTRING=@SQLSTRING+'FROM [@]..SYSCOLUMNS AS C '
SET @SQLSTRING=@SQLSTRING+'LEFT JOIN [@]..SYSOBJECTS AS O ON O.ID=C.ID '
SET @SQLSTRING=@SQLSTRING+'LEFT JOIN SYSOBJECTS AS LO ON LO.NAME=O.NAME '
SET @SQLSTRING=@SQLSTRING+'LEFT JOIN SYSCOLUMNS AS LC ON LC.ID=LO.ID AND LC.NAME=C.NAME '
SET @SQLSTRING=@SQLSTRING+'WHERE O.TYPE=''U'' AND LC.NAME IS NULL '
SET @SQLSTRING=@SQLSTRING+'UNION '
SET @SQLSTRING=@SQLSTRING+'SELECT ''现【 ''+C.NAME+'' 】'' AS ''差异列名'' ,''现【 ''+O.NAME+'' 】'' AS ''列所在表：'' '
SET @SQLSTRING=@SQLSTRING+'FROM SYSCOLUMNS AS C '
SET @SQLSTRING=@SQLSTRING+'LEFT JOIN SYSOBJECTS AS O ON O.ID=C.ID '
SET @SQLSTRING=@SQLSTRING+'LEFT JOIN [@]..SYSOBJECTS AS LO ON LO.NAME=O.NAME '
SET @SQLSTRING=@SQLSTRING+'LEFT JOIN [@]..SYSCOLUMNS AS LC ON LC.ID=LO.ID AND LC.NAME=C.NAME '
SET @SQLSTRING=@SQLSTRING+'WHERE O.TYPE=''U'' AND LC.NAME IS NULL '
SET @SQLSTRING=@SQLSTRING+'END '
SET @SQLSTRING=@SQLSTRING+'END '
SET @SQLSTRING=@SQLSTRING+'ELSE '
SET @SQLSTRING=@SQLSTRING+'BEGIN '
SET @SQLSTRING=@SQLSTRING+'PRINT ''表名是否对应：否'' '
SET @SQLSTRING=@SQLSTRING+'PRINT ''表名差异信息如下：原 [''+CONVERT(NVARCHAR(100),@ODIS_COUNT)+'']''+'' '
SET @SQLSTRING=@SQLSTRING+'现 [''+CONVERT(NVARCHAR(100),@NDIS_COUNT)+'']'' '
SET @SQLSTRING=@SQLSTRING+'SELECT ''原【 ''+NAME+'' 】'' AS ''差异表名'' FROM [@]..SYSOBJECTS WHERE TYPE=''U'' '
SET @SQLSTRING=@SQLSTRING+'AND NAME NOT IN (SELECT NAME FROM SYSOBJECTS WHERE TYPE=''U'') '
SET @SQLSTRING=@SQLSTRING+'UNION '
SET @SQLSTRING=@SQLSTRING+'SELECT ''现【 ''+NAME+'' 】'' AS ''差异表名'' FROM SYSOBJECTS WHERE TYPE=''U'' '
SET @SQLSTRING=@SQLSTRING+'AND NAME NOT IN (SELECT NAME FROM [@]..SYSOBJECTS WHERE TYPE=''U'') '
SET @SQLSTRING=@SQLSTRING+'END '
SET @SQLSTRING = REPLACE(@SQLSTRING,'[@]..','['+@ODB_NAME+']..')
EXEC SP_EXECUTESQL @SQLSTRING