在C＃中比较两个SQL Server数据库架构 [英] Comparing two SQL Server database schema in C#
问题描述
我正在发布Windows应用程序的较新版本。新版本中有数据库架构更改。我也不想丢失数据。
I am releasing a newer version of my Windows application. There are DB schema changes in the new version. Also I do not want to lose the data.
因此，我采取的方法是在保留数据库的同时替换dll。为了升级数据库，我打算比较旧数据库的数据库架构并进行必要的更改。
So the approach that I have taken is to replace the dlls while preserving the database. In order to upgarde the database, I am planning to compare the database schema of the old database and make necessary changes.
所以我如何比较数据库结构（模式）从旧的到新的，我如何检测到更改并进行更正。到目前为止，我尝试过使用 GetSchema 方法获取数据库架构。
So how can I compare the database structure (schema) of old one to new one and how can I detect the changes and correct it. What I have tried so far is try and get the database schema using GetSchema method.
但是，由于新模式是预定义的，因此，如何将新模式注入程序，并与安装较旧版本的站点中的现有模式进行比较。
But since the new schema is a predefined one, How can I inject the new schema into the program and compare with the existing one in the site where older was installed.
推荐答案
这是比较数据库的免费方法。
Here's a free way to compare databases.
下面是一个我敲掉了SQL Server脚本，该脚本将数据库的存储过程，视图和表的内容输出到 Output 窗口中。
Below is a SQL Server script I knocked up, which outputs the contents of your database's Stored Procedures, Views and Tables into the Output window.
您运行它通过调用：
exec [dbo].[ScriptStoredProcedures]
在我的许多项目中，我将运行此脚本，将文本复制到Visual Studio项目中的文件中，这样我就可以检查我们
On many of my projects, I'll run this script, copy the text into a file in my Visual Studio project, so I can check-in a copy of how our database looked at a particular time.
（是的，您还可以在Visual Studio中使用数据库项目，但这是另一种方法。）
(Yes, you can also have Database Projects within Visual Studio, but this is an alternative method.)
如果在两个数据库上都运行此脚本，则可以比较两个输出以发现差异
If you run this script on both of your database, you can compare the two outputs to find differences.
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
这篇关于在C＃中比较两个SQL Server数据库架构的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
