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?
Update: Red gate lists "from $395". Anything free? Or more foolproof than just keeping the version number?
Show activity on this post. Make a table and store your version number in there. Just make sure you update it as necessary. CREATE TABLE version ( version VARCHAR(255) NOT NULL ) INSERT INTO version VALUES ('v1.
To compare database definitions. On the Tools menu, select SQL Server, and then click New Schema Comparison. Alternatively, right-click the TradeDev project in Solution Explorer, and select Schema Compare. The Schema Compare window opens, and Visual Studio automatically assigns it a name such as SqlSchemaCompare1 .
Schema Comparison Tool allows you to compare tables, views, functions, sequences, packages, stored procedures and other database objects between two schemas/databases. It will report any discrepancies between schemas such as missing or mismatching stored procedures, tables, triggers, columns, indexes and constraints.
Try this SQL.
- Run it against each database.
- Save the output to text files.
- Diff the text files.
/* 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
I hope I can help - this is the article I suggest reading:
Compare SQL Server database schemas automatically
It describes how you can automate the SQL Server schema comparison and synchronization process using T-SQL, SSMS or a third party tool.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With