SQL--比较两个数据库的表结构

在开发过程中,经常会遇到测试数据库和正式数据库的表结构不一致的情况
今天总结一下我的解决办法

USE Test_1
DECLARE @tableCount INT
SELECT @tableCount = COUNT(1) FROM [sysobjects] WHERE [xtype] = 'U'
--将此数据库中的所有表名插入到临时表中
SELECT IDENTITY(INT,1,1) AS ID, [name] INTO #TmpTable FROM [sysobjects] WHERE [xtype] = 'U' ORDER BY [name]
--SELECT * FROM #TmpTable

DECLARE @tableName VARCHAR(100)
DECLARE @tableIndex INT
SET @tableIndex = 1
WHILE @tableIndex <= @tableCount
BEGIN
    SELECT @tableName = Name FROM #TmpTable WHERE ID = @tableIndex
    SET @tableIndex = @tableIndex + 1
    --测试库
    USE Test_1
    SELECT
        SO.[name] AS TableName
        ,SC.[name] AS ColumnName
        ,ST.[name] AS ColumnType
        ,COLUMNPROPERTY(SC.[id], SC.[name], 'PRECISION') AS ColumnLength
        ,CASE SC.[isnullable]
            WHEN 1 THEN '√'
            ELSE ''
            END AS IsAllowNull
        ,ISNULL(SCM.text, '') AS DefaultValue
    INTO #TmpTable1
    FROM [syscolumns] SC
    JOIN [sysobjects] SO ON SO.id = SC.id AND SO.[xtype] = 'U'
    LEFT JOIN [systypes] ST ON ST.[xusertype] = SC.[xtype]
    LEFT JOIN [syscomments] SCM ON SCM.[id] = SC.[cdefault]
    WHERE SO.[name] = @tableName
    ORDER BY SC.[colid]
    --正式库
    USE Test_2
    SELECT
        SC.[name] AS ColumnName
        ,ST.[name] AS ColumnType
        ,COLUMNPROPERTY(SC.[id], SC.[name], 'PRECISION') AS ColumnLength
        ,CASE SC.[isnullable]
            WHEN 1 THEN '√'
            ELSE ''
            END AS IsAllowNull
        ,ISNULL(SCM.text, '') AS DefaultValue
    INTO #TmpTable2
    FROM [syscolumns] SC
    JOIN [sysobjects] SO ON SO.id = SC.id AND SO.[xtype] = 'U'
    LEFT JOIN [systypes] ST ON ST.[xusertype] = SC.[xtype]
    LEFT JOIN [syscomments] SCM ON SCM.[id] = SC.[cdefault]
    WHERE SO.[name] = @tableName
    ORDER BY SC.[colid]

    IF EXISTS (
        SELECT * FROM #TmpTable1 A
        FULL OUTER JOIN #TmpTable2 B ON A.ColumnName = B.ColumnName
        WHERE A.ColumnName <> B.ColumnName OR A.ColumnType <> B.ColumnType OR A.ColumnLength <> B.ColumnLength OR A.IsAllowNull <> B.IsAllowNull OR A.DefaultValue <> B.DefaultValue OR A.ColumnName IS NULL OR B.ColumnName IS NULL
    )
    SELECT * FROM #TmpTable1 A
    FULL OUTER JOIN #TmpTable2 B ON A.ColumnName = B.ColumnName
    WHERE A.ColumnName <> B.ColumnName OR A.ColumnType <> B.ColumnType OR A.ColumnLength <> B.ColumnLength OR A.IsAllowNull <> B.IsAllowNull OR A.DefaultValue <> B.DefaultValue OR A.ColumnName IS NULL OR B.ColumnName IS NULL

    DROP TABLE #TmpTable1
    DROP TABLE #TmpTable2
END

DROP TABLE #TmpTable

运行结果

阅读更多
版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/yzxxxx/article/details/72626746
文章标签: 数据库 表结构
个人分类: SQL-Server
上一篇SQL--删除表数据的三种不同实现方法
想对作者说点什么? 我来说一句

对比两个数据库表结构

2010年08月31日 72KB 下载

比较两个数据库表结构 sql

2010年11月19日 5KB 下载

两个数据库表结构比较(C#)

2010年06月25日 551KB 下载

没有更多推荐了,返回首页

关闭
关闭