比较两个数据库表结构的差异

原创

山可可 2023-02-28 10:55:19 ©著作权

文章标签 数据库 join null sql table 文章分类 Html/CSS 前端开发

©著作权归作者所有：来自51CTO博客作者山可可的原创作品，请联系作者获取转载授权，否则将追究法律责任

1、简易法：

使用以下SQL语句：

SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE,
IS_NULLABLE, COLUMNPROPERTY(OBJECT_ID(TABLE_NAME), COLUMN_NAME, 'IsIdentity') AS IS_AUTOINCREMENT,
COLUMN_DEFAULT
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'yourTableName'
ORDER BY ORDINAL_POSITION

将yourTableName换成你自己的表名，由于是两个，所以你需要在SQL Server企业管理器查询中执行一次之后，将相关字段COPY出来，比如：你只需要比较字段名称是否一致，则上面改成以下即可：

SELECT COLUMN_NAME

FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'yourTableName'
ORDER BY ORDINAL_POSITION

然后选中COLUMN_NAME，复制到文本文件或WORD表格（先在WORD中画一个两列的表格即可）的左列一侧。

另一个表则再执行上面SQL语句一次，然后选中COLUMN_NAME，复制到文本中或WORD表格的右列一侧。

接下来就是你进行比较了。不再多说。

2、复杂代码法：

/*****代码开始*****/

if   exists   (select   *   from   dbo.sysobjects   where   id   =   object_id(N'[dbo].[p_comparestructure]')   and   OBJECTPROPERTY(id,   N'IsProcedure')   =   1)
drop   procedure   [dbo].[p_comparestructure]
GO

/*--比较两个数据库的表结构差异

可以比较两个数据库的结构差异

--邹建   2003.9(引用请保留此信息)--*/

/*--调用示例

exec   p_comparestructure   '库1','库2'
--*/
create   proc   p_comparestructure
@dbname1   varchar(250), --要比较的数据库名1
@dbname2   varchar(250) --要比较的数据库名2
as
create   table   #tb1(表名1   varchar(250),字段名   varchar(250),序号   int,标识   bit,主键   bit,类型   varchar(250),
占用字节数   int,长度   int,小数位数   int,允许空   bit,默认值   sql_variant,字段说明   sql_variant)

create   table   #tb2(表名2   varchar(250),字段名   varchar(250),序号   int,标识   bit,主键   bit,类型   varchar(250),
占用字节数   int,长度   int,小数位数   int,允许空   bit,默认值   sql_variant,字段说明   sql_variant)

--得到数据库1的结构
exec('insert   into   #tb1   SELECT
表名=d.name,字段名=a.name,序号=a.colid,
标识=case   when   a.status=0x80   then   1   else   0   end,
主键=case   when   exists(SELECT   1   FROM   '+@dbname1+'..sysobjects   where   xtype=''PK''   and   name   in   (
SELECT   name   FROM   '+@dbname1+'..sysindexes   WHERE   indid   in(
SELECT   indid   FROM   '+@dbname1+'..sysindexkeys   WHERE   id   =   a.id   AND   colid=a.colid
)))   then   1   else   0   end,
类型=b.name, 占用字节数=a.length,长度=a.prec,小数位数=a.scale, 允许空=a.isnullable,
默认值=isnull(e.text,''''),字段说明=isnull(g.[value],'''')
FROM   '+@dbname1+'..syscolumns   a
left   join   '+@dbname1+'..systypes   b   on   a.xtype=b.xusertype
inner   join   '+@dbname1+'..sysobjects   d   on   a.id=d.id     and   d.xtype=''U''   and     d.name<>''dtproperties''
left   join   '+@dbname1+'..syscomments   e   on   a.cdefault=e.id
left   join   '+@dbname1+'..sysproperties   g   on   a.id=g.id   and   a.colid=g.smallid
order   by   a.id,a.colorder')

--得到数据库2的结构
exec('insert   into   #tb2   SELECT
表名=d.name,字段名=a.name,序号=a.colid,
标识=case   when   a.status=0x80   then   1   else   0   end,
主键=case   when   exists(SELECT   1   FROM   '+@dbname2+'..sysobjects   where   xtype=''PK''   and   name   in   (
SELECT   name   FROM   '+@dbname2+'..sysindexes   WHERE   indid   in(
SELECT   indid   FROM   '+@dbname2+'..sysindexkeys   WHERE   id   =   a.id   AND   colid=a.colid
)))   then   1   else   0   end,
类型=b.name, 占用字节数=a.length,长度=a.prec,小数位数=a.scale, 允许空=a.isnullable,
默认值=isnull(e.text,''''),字段说明=isnull(g.[value],'''')
FROM   '+@dbname2+'..syscolumns   a
left   join   '+@dbname2+'..systypes   b   on   a.xtype=b.xusertype
inner   join   '+@dbname2+'..sysobjects   d   on   a.id=d.id     and   d.xtype=''U''   and     d.name<>''dtproperties''
left   join   '+@dbname2+'..syscomments   e   on   a.cdefault=e.id
left   join   '+@dbname2+'..sysproperties   g   on   a.id=g.id   and   a.colid=g.smallid
order   by   a.id,a.colorder')
--and   not   exists(select   1   from   #tb2   where   表名2=a.表名1)
select   比较结果=case   when   a.表名1   is   null   and   b.序号=1   then   '库1缺少表：'+b.表名2
when   b.表名2   is   null   and   a.序号=1   then   '库2缺少表:'+a.表名1
when   a.字段名   is   null   and   exists(select   1   from   #tb1   where   表名1=b.表名2)   then   '库1   ['+b.表名2+']   缺少字段：'+b.字段名
when   b.字段名   is   null   and   exists(select   1   from   #tb2   where   表名2=a.表名1)   then   '库2   ['+a.表名1+']   缺少字段：'+a.字段名
when   a.标识<>b.标识   then   '标识不同'
when   a.主键<>b.主键   then   '主键设置不同'
when   a.类型<>b.类型   then   '字段类型不同'
when   a.占用字节数<>b.占用字节数   then   '占用字节数'
when   a.长度<>b.长度   then   '长度不同'
when   a.小数位数<>b.小数位数   then   '小数位数不同'
when   a.允许空<>b.允许空   then   '是否允许空不同'
when   a.默认值<>b.默认值   then   '默认值不同'
when   a.字段说明<>b.字段说明   then   '字段说明不同'
else   ''   end,
*
from   #tb1   a
full   join   #tb2   b   on   a.表名1=b.表名2   and   a.字段名=b.字段名
where   a.表名1   is   null   or   a.字段名   is   null   or   b.表名2   is   null   or   b.字段名   is   null
or   a.标识<>b.标识   or   a.主键<>b.主键   or   a.类型<>b.类型
or   a.占用字节数<>b.占用字节数   or   a.长度<>b.长度   or   a.小数位数<>b.小数位数
or   a.允许空<>b.允许空   or   a.默认值<>b.默认值   or   a.字段说明<>b.字段说明
order   by   isnull(a.表名1,b.表名2),isnull(a.序号,b.序号)--isnull(a.字段名,b.字段名)
go
/*****代码结束*****/