Tablediff using -strict option complains about different schema. But when scripting the schema's and doing a file compare it's equal.

Question

Dear,

When using tablediff -strict mode, I encounter a strange to me situation.

When generating the export like this for the duplicate table:
$TableExportoptions = New-DbaScriptingOption

$TableExportoptions.ClusteredIndexes = $true

$TableExportoptions.Default = $true

$TableExportoptions.FullTextIndexes = $true

$TableExportoptions.Indexes = $true

$TableExportoptions.NonClusteredIndexes = $true

$TableExportoptions.SchemaQualify = $true

$TableExportoptions.ScriptSchema = $true

$TableExportoptions.ColumnStoreIndexes = $true

$TableExportoptions.Statistics = $true

$TableExportoptions.Triggers = $true

$TableExportoptions.WithDependencies = $true

$TableExportoptions.DriAll = $true

-> Tablediff -strict says the schema's are different and it cannot continue it's effort.

When I choose to omit the indexes in the export settings:

$TableExportoptions = New-DbaScriptingOption

$TableExportoptions.ClusteredIndexes = $true

$TableExportoptions.Default = $true

$TableExportoptions.FullTextIndexes = $true

$TableExportoptions.Indexes = $FALSE

$TableExportoptions.NonClusteredIndexes = $FALSE

$TableExportoptions.SchemaQualify = $true

$TableExportoptions.ScriptSchema = $true

$TableExportoptions.ColumnStoreIndexes = $true

$TableExportoptions.Statistics = $true

$TableExportoptions.Triggers = $true

$TableExportoptions.WithDependencies = $true

$TableExportoptions.DriAll = $true

-> Tablediff -strict is happy

Is it possible to understand what exactly is being checked?

And more importantly what causes it to say it's not the same schema?

I've used TableDiff version 2019 and 2022.

If somebody can get in touch with me, would be appreciated.

Many thanks,

Tim

Answer 1

Hi，Tim Braes

#$TableExportoptions.Indexes = $true #$TableExportoptions.NonClusteredIndexes = $true

Even if you comment out these two lines, the default value will still be executed at runtime. Judging from the results of two runs, the default value should be True.

Comparing the results of the two runs, the two tables should differ in their indexes. The check will only pass when the number of indexes, index types (clustered, non-clustered, columnstore, etc.), the columns included in the indexes and their order, and other index properties (such as uniqueness, fill factor, filter expressions, etc.) are all the same.

You can manually check the index differences between two tables using T-SQL：

USE [TestDatabase]
SELECT i.name AS IndexName, 
OBJECT_NAME(i.object_id) AS TableName, 
COL_NAME(ic.object_id, ic.column_id) AS ColumnName, 
i.type_desc AS IndexType,
ic.index_column_id AS IndexColumnId,
i.is_primary_key AS IsPrimaryKey, 
i.is_unique AS IsUnique, 
i.is_unique_constraint AS IsUniqueConstraint 
FROM sys.indexes i INNER JOIN sys.index_columns ic 
ON i.object_id = ic.object_id AND i.index_id = ic.index_id
WHERE i.object_id = OBJECT_ID('[dbo].[TABLENAME]') ORDER BY i.name, ic.index_column_id;

or analyze the differences through a redirected log file.：

tablediff -strict > C:\Logs\analysis.log

Best regards，

Mikey Qiao

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it.

Answer 2

The /strict option appears to be broken.

I have a script to create a database. I run this script on a instance running SQL 2022, and then I modify the script to create a second database on the same instance. Then I run:

"C:\Program Files\Microsoft SQL Server\160\COM\tablediff.exe" /strict -sourceserver .\TJUTVÅ -sourcedatabase NorthDynamic2 -sourcetable Orders -destinationserver .\TJUTVÅ -destinationdatabase NorthDynamic -destinationtable Orders

And I am told that

Table [NorthDynamic2].[dbo].[Orders] on .\TJUTVÅ and Table [NorthDynamic].[dbo].[Orders] on .\TJUTVÅ have different schemas and cannot be compared.

Using Profiler, I can see that tablediff submits:

 SET FMTONLY OFF; SET NO_BROWSETABLE ON; SET FMTONLY ON;SELECT * FROM [dbo].[Orders] WITH (READUNCOMMITTED) WHERE 1=2 SET FMTONLY OFF; SET NO_BROWSETABLE OFF;

It uses SET FMTONLY ON to deduce the schema - but there are better methods to do this. But even then, it should get the same result - but maybe SQL Server is pulling its legs.