c# - 在 C# 中比较两个 SQL Server 数据库模式

Question

我要发布更新版本的 Windows 应用程序。新版本中有数据库架构更改。我也不想丢失数据。

所以我采取的方法是在保留数据库的同时替换 dll。为了升级数据库，我打算比较旧数据库的数据库模式并进行必要的更改。

那么我怎样才能将旧数据库结构(模式)与新数据库进行比较，以及如何检测变化并纠正它。到目前为止我尝试过的是尝试使用 GetSchema 方法获取数据库模式。

但由于新架构是预定义架构，我如何才能将新架构注入(inject)程序并与安装旧架构的站点中的现有架构进行比较。

Answer 1

这是一种比较数据库的免费方法。

下面是我编写的 SQL Server 脚本，它将数据库的存储过程、 View 和表的内容输出到输出窗口。

你通过调用运行它:

exec [dbo].[ScriptStoredProcedures]

在我的许多项目中，我将运行此脚本，将文本复制到我的 Visual Studio 项目中的一个文件中，这样我就可以 checkin 一份我们的数据库在特定时间看起来如何的副本。

(是的，您也可以在 Visual Studio 中使用数据库项目，但这是另一种方法。)

如果您在两个数据库上运行此脚本，您可以比较两个输出以找出差异。

CREATE PROCEDURE [dbo].[ScriptStoredProcedures]
AS
BEGIN
    --
    --  Attempt to create a long SQL script, to Drop, then "CREATE PROCEDURE" on all SPs and "CREATE FUNCTION" on all Functions in this database. 
    --
    --  You can then run this script on a "target" database, and it'll have the latest Stored Procedures & functions
    --  created/updated on it.
    --
    --      exec [dbo].[ScriptStoredProcedures]
    --  
    SET NOCOUNT ON

    PRINT '--'
    PRINT '--  SQL Script, generated by the [ScriptStoredProcedures] Stored Procedure.'
    PRINT '--  Created on ' + convert(nvarchar, GetDate(), 106) + ' ' + convert(nvarchar, GetDate(), 108)
    PRINT '--'
    PRINT '--  This will create/update the Stored Procedures on this database, to bring them up-to-date with the SPs '
    PRINT '--  from the database ''' + DB_NAME() + ''' on the server ''' + @@SERVERNAME + ''''
    PRINT '--'
    PRINT '--'



    --  Create a temporary table, where each record contains one line of Stored Procedure/Function script
    --  (i.e. If you have a Stored Procedure with 30 lines of script in it, we'll create 30 temporary records
    --  to store it)
    CREATE TABLE #tmp 
    (
        [inx] INT IDENTITY(1, 1),
        [text] nvarchar(4000)
    )

    DECLARE @StoredProcedureName NVARCHAR(200)
    DECLARE @StoredProcedureType NVARCHAR(10)
    DECLARE @ExecCommand NVARCHAR(200)
    DECLARE @OneLineOfScript NVARCHAR(4000)

    --  First, get a list of all Stored Procedures & Functions in this database
    DECLARE cursorEachStoredProcedure CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY FOR 
    SELECT [name],              --  Name of the Stored Procedure or Function
           [type]               --  This will contain "FN" if it's a Function, or "P" if it's a Stored Procedure
    FROM sysobjects 
    WHERE (OBJECTPROPERTY(id, N'IsProcedure') = 1
      OR OBJECTPROPERTY(id, N'IsTableFunction') = 1
      OR OBJECTPROPERTY(id, N'IsScalarFunction') = 1
      OR OBJECTPROPERTY(id, N'IsView') = 1)
    AND [name] NOT LIKE 'sp_%'
    AND [name] NOT LIKE 'fn_%'
    ORDER BY [type] DESC,       --  Sort by Stored Procedures first, then functions
             [name]             --  then show the list of SPs/Functions in name order


    OPEN cursorEachStoredProcedure 
    FETCH NEXT FROM cursorEachStoredProcedure INTO @StoredProcedureName, @StoredProcedureType

    --  For each Stored Procedure we've found in our database, create some script to delete the Stored Procedure
    --  from the target database if it exists, then re-create it.
    WHILE (@@FETCH_STATUS = 0) 
    BEGIN 

        PRINT ''
        IF (@StoredProcedureType = 'P')
        BEGIN
            PRINT 'PRINT ''Creating stored procedure: ''''' + @StoredProcedureName + ''''''''
            PRINT ''
            PRINT 'IF EXISTS(select Name from sysobjects where OBJECTPROPERTY(id, N''IsProcedure'') = 1 AND Name = ''' + @StoredProcedureName + ''')'
            PRINT 'BEGIN'
            PRINT '   DROP PROCEDURE [' + @StoredProcedureName + '] '
            PRINT 'END'
        END
        ELSE
        IF (@StoredProcedureType = 'V')
        BEGIN
            PRINT 'PRINT ''Creating view: ''''' + @StoredProcedureName + ''''''''
            PRINT ''
            PRINT 'IF EXISTS(select Name from sysobjects where OBJECTPROPERTY(id, N''IsView'') = 1 AND Name = ''' + @StoredProcedureName + ''')'
            PRINT 'BEGIN'
            PRINT '   DROP VIEW [' + @StoredProcedureName + '] '
            PRINT 'END'
        END
        ELSE
        BEGIN
            PRINT 'PRINT ''Creating function: ''''' + @StoredProcedureName + ''''''''
            PRINT ''
            PRINT 'IF EXISTS(select Name from sysobjects where (OBJECTPROPERTY(id, N''IsTableFunction'') = 1 OR OBJECTPROPERTY(id, N''IsScalarFunction'') = 1) AND Name = ''' + @StoredProcedureName + ''')'
            PRINT 'BEGIN'
            PRINT '   DROP FUNCTION [' + @StoredProcedureName + '] '
            PRINT 'END'
        END         
        PRINT 'GO '

        --  Run the "sp_helptext" command, to get the text of this Stored Procedure (one row per *line* of script)
        --  and store this set of results in a temporary table, so we can step through, line-by-line, and send
        --  the output to the Messages window.
        SET @ExecCommand = 'sp_helptext @objname = ''' + @StoredProcedureName + ''''

        DELETE FROM #tmp

        INSERT INTO #tmp
        EXEC(@ExecCommand)

        --  Step through each line of this Stored Procedure
        DECLARE cursorEachLineOfScript CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY FOR 
        SELECT [text] 
        FROM #tmp
        ORDER BY [inx]

        OPEN cursorEachLineOfScript 
        FETCH NEXT FROM cursorEachLineOfScript INTO @OneLineOfScript

        WHILE (@@FETCH_STATUS = 0) 
        BEGIN 
            --  For each line of Stored Procedure script, send the text to the Messages window
            PRINT @OneLineOfScript

            FETCH NEXT FROM cursorEachLineOfScript INTO @OneLineOfScript
        END 
        CLOSE cursorEachLineOfScript 
        DEALLOCATE cursorEachLineOfScript   
        PRINT 'GO '

        FETCH NEXT FROM cursorEachStoredProcedure INTO @StoredProcedureName, @StoredProcedureType
    END

    CLOSE cursorEachStoredProcedure 
    DEALLOCATE cursorEachStoredProcedure    

    DROP TABLE #tmp 

    PRINT 'EXEC [dbo].[spGrantExectoAllStoredProcs]'
    PRINT 'GO'

    PRINT '--'
    PRINT '--'
    PRINT '--  List of tables (and their fields) in this database'
    PRINT '--'
    PRINT '--'
    PRINT '--'


    --  First, let's iterate through our list of tables, and find out which fields they contain.
    DECLARE 
        @tableName nvarchar(200),
        @fieldName nvarchar(500),
        @fieldType nvarchar(500),
        @fieldNullable nvarchar(200)

    DECLARE cursorTables CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY FOR 
    SELECT st.NAME as 'Table_name'
    FROM sys.tables st
    ORDER BY 1

    OPEN cursorTables 
    FETCH NEXT FROM cursorTables INTO @tableName

    WHILE (@@FETCH_STATUS = 0) 
    BEGIN
        PRINT '--  Table: ' + @tableName

        DECLARE cursorFields CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY FOR 
        SELECT sc.NAME as 'Field_name',
            case when t.Name in ('char', 'varchar', 'nvarchar') 
           then t.Name + '(' + cast(sc.max_length/2 as nvarchar) + ')' 
           else 
                case when t.Name in ('numeric') 
                    then t.Name + '(' + cast(sc.precision as nvarchar)  + ',' + cast(sc.scale as nvarchar) + ')'  
                    else t.Name 
                end
        end as 'Data_type',
        case when sc.is_nullable=1 then 'null' else 'not null' end as 'Nullable'
        FROM sys.tables st
        INNER JOIN sys.columns sc ON st.object_id = sc.object_id
        INNER JOIN sys.types t ON sc.system_type_id = t.system_type_id
        WHERE t.Name != 'sysname'
        AND st.name = @tableName
        ORDER BY 1, 2

        OPEN cursorFields 
        FETCH NEXT FROM cursorFields INTO @fieldName, @fieldType, @fieldNullable

        WHILE (@@FETCH_STATUS = 0) 
        BEGIN
            PRINT '--    ' + @fieldName + '  (' + @fieldType + ', ' + @fieldNullable + ')'
            FETCH NEXT FROM cursorFields INTO @fieldName, @fieldType, @fieldNullable
        END
        CLOSE cursorFields 
        DEALLOCATE cursorFields 

        PRINT '--'

        FETCH NEXT FROM cursorTables INTO @tableName
    END
    CLOSE cursorTables 
    DEALLOCATE cursorTables 
END