How can I sort the output of the generated SQL script?

Question

I am comparing two SQL server databases (development and live, SQL2005 and SQL2008, respectively) to check the differences between them. If I create a script for each database, I can use a simple text comparison to highlight the differences.

The problem is that the scripts should be in the same order to simplify the comparison and avoid simple differences when the order of the stored procedures is different, but their contents are the same.

So, if I create this from development:

1: CREATE TABLE dbo.Table1 (ID INT NOT NULL, Name VARCHAR(100) NULL) 2: CREATE TABLE dbo.Table2 (ID INT NOT NULL, Name VARCHAR(100) NULL) 3: CREATE TABLE dbo.Table3 (ID INT NOT NULL, Name VARCHAR(100) NULL) 

And this is from the living:

 1: CREATE TABLE dbo.Table1 (ID INT NOT NULL, Name VARCHAR(100) NULL) 2: CREATE TABLE dbo.Table3 (ID INT NOT NULL, Name VARCHAR(100) NULL) 3: CREATE TABLE dbo.Table2 (ID INT NOT NULL, Name VARCHAR(100) NULL) 

Comparison of the two main lines 2 and 3 as different, but they are actually identical, only the script generation wizard made table3 before table 2 in a live environment. Add to 100 tables, stored procedures, views, etc., and it quickly becomes a mess.

My current settings are:

• Manually sort contents before comparing
• Create a scripting program in a specific order
• Find a free app that sorts your scripts
• Pay for a product that does this as part of its toolbox.
• (Another way to do it)

Hopefully I miss the checkbox that says “Sort scripts by name”, but I don’t see anything that does this. I don’t feel that I will have to pay for something as simple as the “sort” option or many other unnecessary tools, so option 4 should only be the last.

EDIT I ​​have full access to both environments, but the live environment is locked and hosted on remote desktop virtual servers for normal access to live. My preference is to copy what I can, into development and compare there. I can generate scripts for each type of object in the database as separate files (tables, SP, functions, etc.).

Answer 1

Depending on your version of Visual Studio 2010 (if any), you can do this easily through the data menu, based on your initial intention, you can save some time.

Change Generating the actual database and then comparing the schema comparison tool as shown below is the same clean effect as comparing the two script files, and you don't need to worry about line breaks, etc.

Answer 2

Red_gate SQLCompare is the best thing for it, worth every penny.

Answer 3

You can use WinMerge to some extent to find out if lines are found elsewhere when comparing two generated scripts. I think this works in simpler cases.

Using WinMerge v2.12.4.0 Unicode. Note the use of color to highlight these below.

Here is the help for Edit -> Options -> Compare "Enable Detection of Moved Block":

3.6. Enable Displaced Block Detection Disabled (default): WinMerge does not detect when differences are associated with displaced lines.

Enabled: WinMerge attempts to detect lines that are moving (at different locations in each file). Displaced blocks are indicated by displacement and Selected Displaced Difference Colors. If the location bar is displayed, the corresponding differential locations in the left and right bars are associated with the line. Showing relocated blocks can make it easier to visualize changes to files if there aren't many.

For an example, see the description of the Location pane in the Compare and Merge Files section.

Answer 4

This is quite difficult to do with scripts - since SQL will tend to generate tables / objects in the order that makes sense to it (for example, the order of dependencies), rather than alphabetically.

There are other complications that arise when starting a database comparison - for example, the names of constraint objects can be randomly generated, so the same constraint can have different names in each database.

Best of all, probably option (4) I'm afraid ... a copy of evaulation Red Gate Sql Compare is free for 30 days. I used it a lot and very well defined the differences that matter. Then it will generate a script for you to return the two schemas back.

edit: either Visual Studio 2010 Ultimate (or Premium) can do this, apparently - see kd7 answer

Answer 5

I had a similar problem. My database is SQL Server 2008. I realized that if I create scripts with detailed information about the object, I get this order, which I look up the names of. Thus, I was able to compare 2 databases and find out their differences.

The only problem is that I had to take out separate scripts for tables / stored procedures, triggers, etc.

But we can easily compare.