Example - selecting single tables for comparison
Published 04 June 2013
This shows how you can use the command line or an XML arguments file to select a single table for comparison.
In this example, the databases contain the following tables (amongst others):
- Product
- ProductCategory
- ProductCostHistory
- ProductDealerPriceHistory
- SpecialOfferProduct
You are interested only in the schema differences between the Product tables in two different versions of your database; you are not interested in any of the other tables, or any other objects in the database.
You can replace the database (/db) in this example with either a scripts folder (/scr) or a snapshot (/sn) and the example will still work correctly. For more information, see: Simple examples using the command line.
Using the command line
To specify the table to include, use the /include switch:
sqlcompare /db1:Products1 /db2:Products2 /Include:table /Include:table:\[Product\] /verbose
where:
/db1:Products1
specifies that you want to compare the database Products1
/db2:Products2
specifies that you want to compare the database Products2
/Include:table
specifies that you want to compare only tables; you do not want to compare other objects such as views, stored procedures, and so on. If you omit this argument, SQL Compare compares all tables that have names that match the second /Include switch and all other objects in the databases.
To specify more than one object type for inclusion, use multiple /include switches. For example, to include only tables and views, enter:
/Include:table /Include:view
/Include:table:\[Product\]
specifies that you want to compare only the table that has a name that includes the string[Product]
You must include the brackets ( [ ] ) in the string; if you specify the argument without the brackets, /Include:table:Product, theProductCategory table is included because it contains the string Product. The full SQL Server table names are qualified by the owner name in SQL Server 2000, and the schema name in SQL Server 2005/2008, and include brackets. For example (in SQL Server 2000):
[dbo].[Product]
[dbo].[ProductCategory]
and so on. Therefore, the brackets indicate that you are specifying the full table name. To include the owner (or schema) name in the regular expression, you would need also to escape the dot ( .
):
/Include:table:\[dbo\]\ .\[Product\]
The pipe character ( | ) in a regular expression is interpreted as a logical OR. The character must be escaped by the caret character ( ^ ), to prevent the operating system shell from interpreting it as the pipe operator. (If you want to use the caret character itself as part of your regular expression, it must be escaped by a second caret.)
/Verbose
specifies that you want to display detailed information about differences between objects
Using XML
You can use XML like this:
<?xml version="1.0"?> <commandline> <database1>Products1</database1> <database2>Products2</database2> <verbose/> <include>Table</include> <include>Table:\[Product\]</include> </commandline>
To execute the comparison using the XML file, enter the following command:
sqlcompare /Argfile:XMLFileName.xml
where XMLFileName is the name of the XML file.