blocks|key|360097|text|创建一个表，并在其中存储您的版本号。只要确保在必要时进行更新即可。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|360098|CREATE+TABLE+version+(
++++version+VARCHAR(255)+NOT+NULL
)
INSERT+INTO+version+VALUES+('v1.0');|code-block|syntax|javascript|360099|然后，您可以在应用程序设置期间或任何方便的地方检查存储在数据库中的版本号是否与应用程序代码匹配。|360100|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

Make a table and store your version number in there. Just make sure you update it as necessary. 

<pre><code>CREATE TABLE version (
 version VARCHAR(255) NOT NULL
)
INSERT INTO version VALUES ('v1.0');
</code></pre>

You can then check the version number stored in the database matches the application code during your app's setup or wherever is convenient.

blocks|key|3151826|text|SQL+Compare+by+Red+Gate。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|3151827|entityMap|0|LINK|mutability|MUTABLE|url|http://www.red-gate.com/products/SQL_Compare/index.htm^0|0|N|0|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@$A|M|B|N|1|O]]|C|$]]|$1|D|3|-4|5|6|7|P|8|@]|9|@]|C|$]]]|E|$F|$5|G|H|I|C|$J|K]]]]

<a href="http://www.red-gate.com/products/SQL_Compare/index.htm" rel="nofollow noreferrer">SQL Compare by Red Gate</a>.

blocks|key|362142|text|如果您正在寻找一个可以比较两个数据库并向您展示Red+Gate与SQL+Compare之间的差异的工具|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|362143|entityMap|0|LINK|mutability|MUTABLE|url|http://www.red-gate.com/products/SQL_Compare/index.htm^0|W|B|0|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@$A|M|B|N|1|O]]|C|$]]|$1|D|3|-4|5|6|7|P|8|@]|9|@]|C|$]]]|E|$F|$5|G|H|I|C|$J|K]]]]

If you are looking for a tool that can compare two databases and show you the difference Red Gate makes <a href="http://www.red-gate.com/products/SQL_Compare/index.htm" rel="nofollow noreferrer">SQL Compare</a>

blocks|key|360121|text|您可以通过查找数据字典(sys.objects、sys.columns等)以编程方式完成此操作。对两个数据库进行比较。但是，也有像Redgate+SQL+Compare+Pro这样的工具可以为您做这件事。现在，我已经将其指定为数据仓库系统上的QA工具的一部分，包括我目前正在处理的那个。在我目前的工作中，这完全没有问题，因为这里的DBA已经在使用它了。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|360122|使用这些工具的基本方法是维护一个构建数据库的参考脚本，并将其保持在版本控制中。将该脚本运行到一个临时数据库中，并将其与您的目标进行比较，以查看差异。如果你愿意的话，它还会生成补丁脚本。|360123|据我所知，除非你想写自己的东西，否则没有免费的东西可以做到这一点。Redgate足够便宜，还不如是免费的。即使作为一个QA工具来证明生产数据库不在预期的配置中，它也会在一次事件后为您节省购买价格。|360124|entityMap|0|LINK|mutability|MUTABLE|url|http://www.red-gate.com/products/SQL_Compare/index.htm^0|1T|N|0|0|0|0^^$0|@$1|2|3|4|5|6|7|P|8|@]|9|@$A|Q|B|R|1|S]]|C|$]]|$1|D|3|E|5|6|7|T|8|@]|9|@]|C|$]]|$1|F|3|G|5|6|7|U|8|@]|9|@]|C|$]]|$1|H|3|-4|5|6|7|V|8|@]|9|@]|C|$]]]|I|$J|$5|K|L|M|C|$N|O]]]]

You can do it programatically by looking in the data dictionary (sys.objects, sys.columns etc.) of both databases and comparing them. However, there are also tools like <a href="http://www.red-gate.com/products/SQL_Compare/index.htm" rel="noreferrer">Redgate SQL Compare Pro</a> that do this for you. I have specified this as a part of the tooling for QA on data warehouse systems on a few occasions now, including the one I am currently working on. On my current gig this was no problem at all, as the DBA's here were already using it.

The basic methodology for using these tools is to maintain a reference script that builds the database and keep this in version control. Run the script into a scratch database and compare it with your target to see the differences. It will also generate patch scripts if you feel so inclined.

As far as I know there's nothing free that does this unless you feel like writing your own. Redgate is cheap enough that it might as well be free. Even as a QA tool to prove that the production DB is not in the configuration it was meant to be it will save you its purchase price after one incident.

blocks|key|362152|text|这是哪种RDBMS+?潜在的变化有多复杂？|type|unstyled|depth|inlineStyleRanges|entityRanges|data|362153|也许这只是比较每个表的行数和索引数的问题--如果还需要考虑触发器和存储过程的版本，那么就需要更多的实用化信息|362154|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

Which RDBMS is this, and how complex are the potential changes?

Maybe this is just a matter of comparing row counts and index counts for each table -- if you have trigger and stored procedure versions to worry about also then you need something more industrial

blocks|key|362156|text|试试这个SQL。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|362157|362158|对每个数据库运行它。|unordered-list-item|362159|将输出保存到文本文件。|362160|比较文本文件。|362161|362162|/*+get+list+of+objects+in+the+database+*/
SELECT+name,+
+++++++type+
FROM++sysobjects
ORDER+BY+type,+name

/*+get+list+of+columns+in+each+table+/+parameters+for+each+stored+procedure+*/
SELECT+so.name,+
+++++++so.type,+
+++++++sc.name,+
+++++++sc.number,+
+++++++sc.colid,+
+++++++sc.status,+
+++++++sc.type,+
+++++++sc.length,+
+++++++sc.usertype+,+
+++++++sc.scale+
FROM+++sysobjects++so+,+
+++++++syscolumns++sc+
WHERE++so.id+=+sc.id+
ORDER+BY+so.type,+so.name,+sc.name

/*+get+definition+of+each+stored+procedure+*/
SELECT+so.name,+
+++++++so.type,+
+++++++sc.number,+
+++++++sc.text+
FROM+++sysobjects++so+,+
+++++++syscomments+sc+
WHERE++so.id+=+sc.id+
ORDER+BY+so.type,+so.name,+sc.number+|code-block|syntax|javascript|362163|entityMap^0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|R|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|S|8|@]|9|@]|A|$]]|$1|C|3|D|5|E|7|T|8|@]|9|@]|A|$]]|$1|F|3|G|5|E|7|U|8|@]|9|@]|A|$]]|$1|H|3|I|5|E|7|V|8|@]|9|@]|A|$]]|$1|J|3|-4|5|6|7|W|8|@]|9|@]|A|$]]|$1|K|3|L|5|M|7|X|8|@]|9|@]|A|$N|O]]|$1|P|3|-4|5|6|7|Y|8|@]|9|@]|A|$]]]|Q|$]]

Try this SQL. 
- Run it against each database. 
- Save the output to text files. 
- Diff the text files. 

<pre><code>/* get list of objects in the database */
SELECT name, 
 type 
FROM sysobjects
ORDER BY type, name

/* get list of columns in each table / parameters for each stored procedure */
SELECT so.name, 
 so.type, 
 sc.name, 
 sc.number, 
 sc.colid, 
 sc.status, 
 sc.type, 
 sc.length, 
 sc.usertype , 
 sc.scale 
FROM sysobjects so , 
 syscolumns sc 
WHERE so.id = sc.id 
ORDER BY so.type, so.name, sc.name

/* get definition of each stored procedure */
SELECT so.name, 
 so.type, 
 sc.number, 
 sc.text 
FROM sysobjects so , 
 syscomments sc 
WHERE so.id = sc.id 
ORDER BY so.type, so.name, sc.number 
</code></pre>

blocks|key|3364751|text|您没有提到您正在使用哪个RDMBS+:如果信息模式视图在您的RDBMS中可用，并且如果您可以引用来自同一主机的两个模式，则可以查询信息模式视图以识别以下方面的差异：-tables+-columns+-column+types+-constraints+(例如，主键、唯一约束、外键等)|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3364752|我在SQL+Server上为过去的一个作业编写了一组查询，正是出于这个目的-它很好地识别了差异。许多查询使用左连接和IS+NULL来检查是否缺少预期的项，其他查询则比较列类型或约束名称等内容。|3364753|这有点单调乏味，但这是可能的。|3364754|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|H|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|I|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|J|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|G|$]]

You didn't mention which RDMBS you're using: if the INFORMATION SCHEMA views are available in your RDBMS, and if you can reference both schemas from the same host, you can query the INFORMATION SCHEMA views to identify differences in:
-tables
-columns
-column types
-constraints (e.g. primary keys, unique constraints, foreign keys, etc)

I've written a set of queries for exactly this purpose on SQL Server for a past job - it worked well to identify differences. Many of the queries were using LEFT JOINs with IS NULL to check for the absence of expected items, others were comparing things like column types or constraint names.

It's a little tedious, but its possible.

blocks|key|360204|text|试试dbForge+Data+Compare+for+SQL+Server。它可以比较和同步任何数据库，甚至是非常大的数据库。快速，简单，总是提供正确的结果。在您的数据库上试用它，并对产品进行评论。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|360205|我们可以向您推荐一个可靠的SQL比较工具，它可以将SQL+Server数据库中的表数据的比较和同步速度提高3倍。这是SQL+Server的dbForge数据比较。|360206|主要优势：|360207|用于调整比较和databases|360208|Support比较和同步报告的表、列和schemas|unordered-list-item|360209|
|360210|Multiple选项的|360211|360212|表、列和schemas|360213|360214|Multiple的自定义映射更快地比较和同步本机SQL+Server备份的大型SQL+Server报告|360215|360216|加上30天免费试用和无风险购买，并提供30天退款保证。|360217|entityMap|0|LINK|mutability|MUTABLE|url|http://www.devart.com/dbforge/sql/datacompare/^0|2|Z|0|0|0|0|0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|17|8|@]|9|@$A|18|B|19|1|1A]]|C|$]]|$1|D|3|E|5|6|7|1B|8|@]|9|@]|C|$]]|$1|F|3|G|5|6|7|1C|8|@]|9|@]|C|$]]|$1|H|3|I|5|6|7|1D|8|@]|9|@]|C|$]]|$1|J|3|K|5|L|7|1E|8|@]|9|@]|C|$]]|$1|M|3|N|5|6|7|1F|8|@]|9|@]|C|$]]|$1|O|3|P|5|L|7|1G|8|@]|9|@]|C|$]]|$1|Q|3|N|5|6|7|1H|8|@]|9|@]|C|$]]|$1|R|3|S|5|L|7|1I|8|@]|9|@]|C|$]]|$1|T|3|N|5|6|7|1J|8|@]|9|@]|C|$]]|$1|U|3|V|5|L|7|1K|8|@]|9|@]|C|$]]|$1|W|3|-4|5|6|7|1L|8|@]|9|@]|C|$]]|$1|X|3|Y|5|6|7|1M|8|@]|9|@]|C|$]]|$1|Z|3|-4|5|6|7|1N|8|@]|9|@]|C|$]]]|10|$11|$5|12|13|14|C|$15|16]]]]

Try <a href="http://www.devart.com/dbforge/sql/datacompare/" rel="nofollow noreferrer">dbForge Data Compare for SQL Server</a>. It can compare and sync any databases, even very large ones. Quick, easy, always delivers a correct result.
Try it on your database and comment upon the product. 

We can recommend you a reliable SQL comparison tool that offer 3 time’s faster comparison and synchronization of table data in your SQL Server databases. It's dbForge Data Compare for SQL Server.

Main advantages:

<ul>
<li>Speedier comparison and synchronization of large databases</li>
<li>Support of native SQL Server backups</li>
<li>Custom mapping of tables, columns, and schemas</li>
<li>Multiple options to tune your comparison and synchronization</li>
<li>Generating comparison and synchronization reports</li>
</ul>

Plus free 30-day trial and risk-free purchase with 30-day money back guarantee.

blocks|key|3152081|text|您现在可以免费使用my+SQL+Admin+Studio来运行模式比较、数据比较和同步更改。不再需要从此处http://www.simego.com/Products/SQL-Admin-Studio下载许可证密钥|type|unstyled|depth|inlineStyleRanges|offset|length|style|BOLD|entityRanges|data|3152082|也适用于SQL+Azure。|3152083|更新:是的，我是上述程序的作者，因为它现在是免费的，我只是想与社区分享它|3152084|entityMap|0|LINK|mutability|MUTABLE|url|http://www.simego.com/Products/SQL-Admin-Studio^0|C|G|V|4|10|4|1H|1B|0|0|0|0^^$0|@$1|2|3|4|5|6|7|R|8|@$9|S|A|T|B|C]|$9|U|A|V|B|C]|$9|W|A|X|B|C]]|D|@$9|Y|A|Z|1|10]]|E|$]]|$1|F|3|G|5|6|7|11|8|@]|D|@]|E|$]]|$1|H|3|I|5|6|7|12|8|@]|D|@]|E|$]]|$1|J|3|-4|5|6|7|13|8|@]|D|@]|E|$]]]|K|$L|$5|M|N|O|E|$P|Q]]]]

You can now use my SQL Admin Studio for free to run a Schema Compare, Data Compare and Sync the Changes. No longer requires a license key download from here <a href="http://www.simego.com/Products/SQL-Admin-Studio" rel="nofollow">http://www.simego.com/Products/SQL-Admin-Studio</a>

Also works against SQL Azure.

[UPDATE: Yes I am the Author of the above program, as it's now Free I just wanted to Share it with the community]

blocks|key|364781|text|我发现这个小而免费的工具可以满足我的大部分需求。http://www.wintestgear.com/products/MSSQLSchemaDiff/MSSQLSchemaDiff.html|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|364782|它非常基础，但它向您展示了两个数据库的模式差异。它没有任何花哨的东西，比如自动生成的脚本来消除差异，也不比较任何数据。|364783|它只是一个小的、免费的实用程序，可以向您显示模式差异:)|364784|entityMap|0|LINK|mutability|MUTABLE|url|http://www.wintestgear.com/products/MSSQLSchemaDiff/MSSQLSchemaDiff.html^0|O|20|0|0|0|0^^$0|@$1|2|3|4|5|6|7|P|8|@]|9|@$A|Q|B|R|1|S]]|C|$]]|$1|D|3|E|5|6|7|T|8|@]|9|@]|C|$]]|$1|F|3|G|5|6|7|U|8|@]|9|@]|C|$]]|$1|H|3|-4|5|6|7|V|8|@]|9|@]|C|$]]]|I|$J|$5|K|L|M|C|$N|O]]]]

I found this small and free tool that fits most of my needs.
<a href="http://www.wintestgear.com/products/MSSQLSchemaDiff/MSSQLSchemaDiff.html" rel="nofollow">http://www.wintestgear.com/products/MSSQLSchemaDiff/MSSQLSchemaDiff.html</a>

It's very basic but it shows you the schema differences of two databases.
It doesn't have any fancy stuff like auto generated scripts to make the differences to go away and it doesn't compare any data.

It's just a small, free utility that shows you schema differences :)

blocks|key|3364961|text|我希望我能帮上忙--这是我建议阅读的文章：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3364962|Compare+SQL+Server+database+schemas+automatically|offset|length|3364963|它介绍了如何使用T-SQL、SSMS或第三方工具自动执行SQL+Server架构比较和同步过程。|3364964|entityMap|0|LINK|mutability|MUTABLE|url|http://solutioncenter.apexsql.com/compare-sql-server-database-schemas-automatically/^0|0|0|1D|0|0|0^^$0|@$1|2|3|4|5|6|7|P|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|Q|8|@]|9|@$D|R|E|S|1|T]]|A|$]]|$1|F|3|G|5|6|7|U|8|@]|9|@]|A|$]]|$1|H|3|-4|5|6|7|V|8|@]|9|@]|A|$]]]|I|$J|$5|K|L|M|A|$N|O]]]]

I hope I can help - this is the article I suggest reading:

<a href="http://solutioncenter.apexsql.com/compare-sql-server-database-schemas-automatically/" rel="noreferrer">Compare SQL Server database schemas automatically</a>

It describes how you can automate the SQL Server schema comparison and synchronization process using T-SQL, SSMS or a third party tool.

I'm looking for a way to validate the SQL schema on a production DB after updating an application version. If the application does not match the DB schema version, there should be a way to warn the user and list the changes needed.

Is there a tool or a framework (to use programatically) with built-in features to do that?
Or is there some simple algorithm to run this comparison?

<blockquote>
 Update: Red gate lists "from $395". Anything free? Or more foolproof than just keeping the version number?
</blockquote>

how to compare/validate sql schema

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我正在寻找一种在更新应用程序版本后在生产数据库上验证SQL模式的方法。如果应用程序与DB模式版本不匹配，应该有一种方法来警告用户并列出所需的更改。有没有内置功能的工具或框架(以编程方式使用)来实现这一点？或者是否有一些简单的算法来运行这种比较？ 更新：红门列表“$395起”。有什么免费的吗？或者比仅仅保留版本号更简单？

问如何比较/验证sql模式
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何比较/验证sql模式EN