数据库表结构对比

declare @dbname1 varchar(250)
declare @dbname2 varchar(250)
set @dbname1 = '[StandardLMS]'        --需要比对的库名
set @dbname2 = '[StandardLMS_316]'    --需要比对的库名

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[#tb1]') AND type IN (N'U'))
BEGIN
  DROP TABLE #tb1
END
CREATE TABLE #tb1(表名1 varchar(250), 字段名 varchar(250), 序号 int, 标识 bit, 主键 bit, 类型 varchar(250),
  占用字节数 int, 长度 int, 小数位数 int, 允许空 bit, 默认值 varchar(500))

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[#tb2]') AND type IN (N'U'))
BEGIN
  DROP TABLE #tb2
END
CREATE TABLE #tb2(表名2 varchar(250), 字段名 varchar(250), 序号 int, 标识 bit, 主键 bit, 类型 varchar(250),
  占用字节数 int, 长度 int, 小数位数 int, 允许空 bit, 默认值 varchar(500))

--得到数据库1的结构
EXEC('INSERT INTO #tb1
  SELECT 表名1=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, '''')
  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
  ORDER BY a.id,a.colorder')

--得到数据库2的结构
EXEC('INSERT INTO #tb2
  SELECT 表名2=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, '''')
  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
  ORDER BY a.id,a.colorder')

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 '默认值不同'
  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.默认值
  ORDER BY ISNULL(a.表名1, b.表名2), ISNULL(a.序号, b.序号)
  --ISNULL(a.字段名, b.字段名)

DROP TABLE #tb1
DROP TABLE #tb2

GO

转载——————————http://blog.csdn.net/asdf311/