SQL

方法1：

/*--比较两个数据库的表结构差异--*/  /*--调用示例exec p_comparestructure '[YourDB1]','[YourDB2]' --*/create proc p_comparestructure @dbname1varchar(250),--要比较的数据库名1@dbname2varchar(250)--要比较的数据库名2ascreate table #tb1(表名1varchar(250),字段名varchar(250),序号int,标识bit,主键bit,类型varchar(250),占用字节数int,长度int,小数位数int,允许空bit,默认值 sql_variant,字段说明 sql_variant)   create table #tb2(表名2varchar(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 parent_obj=a.id 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 sys.extended_properties g ONa.ID=g.major_id AND a.COLID=g.minor_idorder 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 parent_obj=a.id 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 sys.extended_properties g ONa.ID=g.major_id AND a.COLID=g.minor_id order by a.id,a.colorder')--and not exists (select 1 from #tb2 where 表名2=a.表名1) select 比较结果=case when a.表名1is null and b.序号=1then '库1缺少表：'+b.表名2when b.表名2is null and a.序号=1then '库2缺少表:'+a.表名1when a.字段名is null and exists(select 1from #tb1where 表名1=b.表名2)then '库1 ['+b.表名2+'] 缺少字段：'+b.字段名when b.字段名is null and exists(select 1from #tb2where 表名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.表名2and a.字段名=b.字段名where a.表名1is null or a.字段名is null or b.表名2is null or b.字段名is nullor 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

转载自：http://blog.csdn.net/cowbo/article/details/51774722

================================================================

方法2：

如果需要比较两个数据库的结构，可以参考以下脚本：

使用时请将其中的YourSourceDB及YourTargetDB替换成需要比较的两个DB。

DECLARE @Sourcedb sysname DECLARE @Destdb sysname DECLARE @Tablename sysname DECLARE @SQL varchar(max)  SELECT @Sourcedb = '[YourSourceDB]' SELECT @Destdb   = '[YourTargetDB]' SELECT @Tablename = '%' --  '%' for all tables  SELECT @SQL = ' SELECT Tablename  = ISNULL(Source.tablename,Destination.tablename)                       ,ColumnName = ISNULL(Source.Columnname,Destination.Columnname)                       ,Source.Datatype                       ,Source.Length                       ,Source.precision                       ,Destination.Datatype                       ,Destination.Length                       ,Destination.precision                       ,[Column]  =                        Case                         When Source.Columnname IS NULL then ''Column Missing in the Source''                        When Destination.Columnname IS NULL then ''Column Missing in the Destination''                        ELSE ''''                        end                       ,DataType = CASE WHEN Source.Columnname IS NOT NULL                                          AND Destination.Columnname IS NOT NULL                                          AND Source.Datatype <> Destination.Datatype THEN ''Data Type mismatch''                                    END                       ,Length   = CASE WHEN Source.Columnname IS NOT NULL                                          AND Destination.Columnname IS NOT NULL                                          AND Source.Length <> Destination.Length THEN ''Length mismatch''                                    END                       ,Precision = CASE WHEN Source.Columnname IS NOT NULL                                          AND Destination.Columnname IS NOT NULL                                         AND Source.precision <> Destination.precision THEN ''precision mismatch''                                     END                       ,Collation = CASE WHEN Source.Columnname IS NOT NULL                                          AND Destination.Columnname IS NOT NULL                                         AND ISNULL(Source.collation_name,'''') <> ISNULL(Destination.collation_name,'''') THEN ''Collation mismatch''                                         END                            FROM   (  SELECT Tablename  = so.name        , Columnname = sc.name       , DataType   = St.name       , Length     = Sc.max_length       , precision  = Sc.precision       , collation_name = Sc.collation_name   FROM ' + @Sourcedb + '.SYS.objects So   JOIN ' + @Sourcedb + '.SYS.columns Sc     ON So.object_id = Sc.object_id   JOIN ' + @Sourcedb + '.SYS.types St     ON Sc.system_type_id = St.system_type_id    AND Sc.user_type_id   = St.user_type_id  WHERE SO.TYPE =''U''    AND SO.Name like ''' + @Tablename + '''   ) Source  FULL OUTER JOIN  (   SELECT Tablename  = so.name        , Columnname = sc.name       , DataType   = St.name       , Length     = Sc.max_length       , precision  = Sc.precision       , collation_name = Sc.collation_name   FROM ' + @Destdb + '.SYS.objects So   JOIN ' + @Destdb + '.SYS.columns Sc     ON So.object_id = Sc.object_id   JOIN ' + @Destdb + '.SYS.types St     ON Sc.system_type_id = St.system_type_id    AND Sc.user_type_id   = St.user_type_id WHERE SO.TYPE =''U''   AND SO.Name like ''' + @Tablename + '''  ) Destination   ON source.tablename = Destination.Tablename   AND source.Columnname = Destination.Columnname '  EXEC (@Sql)

转载自：https://gallery.technet.microsoft.com/scriptcenter/12b98927-b464-4330-a8f0-e55ad8998a62