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

Question

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

Tim Braes 0

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

2 answers

Your answer

Answer 1

Erland Sommarskog 132.4K MVP Volunteer Moderator

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.

Erland Sommarskog 132.4K Reputation points MVP Volunteer Moderator

2024-05-02T18:50:20.65+00:00
I played a little more with it, and it seems that the IDENTITY property is the problem. Here is a repro:

USE tempdb go DROP DATABASE TestDB1 DROP DATABASE TestDB2 go CREATE DATABASE TestDB1 go USE TestDB1 go CREATE TABLE sömning (a int NOT NULL IDENTITY, b int NOT NULL, c int NOT NULL, CONSTRAINT pk_sömning PRIMARY KEY (a, b, c) ) go CREATE DATABASE TestDB2 go USE TestDB2 go CREATE TABLE sömning (a int NOT NULL IDENTITY, b int NOT NULL, c int NOT NULL, CONSTRAINT pk_sömning PRIMARY KEY (a, b, c) ) go

Comparing these tables produces the same error. Remove IDENTITY and it works.

Bug filed here: https://feedback.azure.com/d365community/idea/55f6021b-b408-ef11-a73c-0022485313bb

But as this is something that has been there for a long time, don't hold your breath for a fix. If this is a blocking issue for you, you need to open a support case to press for a fix.
Tim Braes 0 Reputation points

2024-05-02T19:59:44.2833333+00:00

Thanks Erland, if you got a good query to compare the schema/contents let me know...
Erland Sommarskog 132.4K Reputation points MVP Volunteer Moderator

2024-05-02T20:21:42.3933333+00:00

It sounds like you are now asking a different question that you did originally. I have not used tablediff a lot, but the documentation makes me think that it is mainly intended for Replication.

I would suggest that you ask a new Question about your underlying problem which you tried to solve with tablediff.

Then again, is there any particularly reason you are using the /strict option?

Answer 2

MikeyQiaoMSFT-0444 3,310

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.

Tim Braes 0 Reputation points

2024-04-30T07:32:05.0233333+00:00
Hi Mike,

Thanks for your response!

The commented out section was an error on my part, it actually got the values (the # was wrongly put in the case)

I now updated it.

I'll try to provide a good repro, thanks a million!

Tim

Tim Braes 0

Hi Mike,

Thanks a bunch for this.

It is clear I'm missing something in the smo exportoptions... but what?

tablediff <option> /strict > analysis.log

type analysis.log

Table [X].[dbo].[Y] on A and Table [X].[dbo].[Y] on B have different schemas and cannot be compared.
The requested operation took 0.147285 seconds.

I was hoping to get more info out tablediff what exactly is the issue (verbose logging).

But apart from telling me they have different schema's... I get nothing out of it.

I also do a quick check using T-SQL which renders me no issues, but I'm missing a property which is clearly not replicated.

 $Query = "
	DECLARE @table as SYSNAME = '$($TableToCopy.tablename)'
    SELECT summary.* FROM
    (
    SELECT 'ROWS' as [CATEGORY], count(1) as [items] from $($TableToCopy.tablename)
    UNION
    SELECT [TYPE_DESC] as [CATEGORY], count(1) as [items] from sys.foreign_keys
    WHERE parent_object_id = object_id(@table)
    GROUP BY [TYPE_DESC]
    UNION
    SELECT 'COLUMNS' as [CATEGORY], count(1) as [items] from sys.columns
    where  object_id = object_id(@table)
    UNION
    SELECT 'STATISTICS' as [CATEGORY], count(1) as [items] from sys.stats
    WHERE  object_id = object_id(@table)
    UNION
    SELECT 'INDEXES' as [CATEGORY], count(1) as [items] from sys.indexes
    WHERE  object_id = object_id(@table)
    UNION
    SELECT [TYPE_DESC] as [CATEGORY], count(1) as [items] from sys.all_objects
    WHERE parent_object_id = object_id(@table)
    GROUP BY [TYPE_DESC]
    ) as summary
    ORDER BY summary.[CATEGORY] ASC
"

When I use Visual Studio Schema compare with default options [did a reset], all is good! (no change required on table level, the deletes, adds have to do with different tables.

My guess here is, same problem here as with the export options?

User's image

So, I'd love to know what tablediff sees differently :-)

Or to know what query tablediff exactly performs, that's equally good.

Thanks,

Tim

MikeyQiaoMSFT-0444 3,310

I used SQL Profiler to trace the command, and the -strict option specifically provided the following query：

SELECT ISC.COLUMN_NAME AS column_name    ,
ISC.DATA_TYPE AS type    ,
ISNULL(ISC.IS_NULLABLE, 0) AS is_nullable    ,
ISNULL(ISC.CHARACTER_MAXIMUM_LENGTH, 0) AS max_length    ,
ISNULL(ISC.NUMERIC_PRECISION, 0) AS numeric_precision    ,
ISNULL(ISC.NUMERIC_SCALE, 0) AS numeric_scale    ,
ISC.COLLATION_NAME AS collation_name    ,
ISNULL(COLUMNPROPERTY(OBJECT_ID(QUOTENAME(ISC.TABLE_SCHEMA) + '.' + QUOTENAME(ISC.TABLE_NAME)), ISC.COLUMN_NAME, 'IsIdentity'), 0) AS is_identity    ,
ISNULL(COLUMNPROPERTY(OBJECT_ID(QUOTENAME(ISC.TABLE_SCHEMA) + '.' + QUOTENAME(ISC.TABLE_NAME)), ISC.COLUMN_NAME, 'IsRowGuidCol'), 0) AS is_rowguid    ,
ISNULL(COLUMNPROPERTY(OBJECT_ID(QUOTENAME(ISC.TABLE_SCHEMA) + '.' + QUOTENAME(ISC.TABLE_NAME)), ISC.COLUMN_NAME, 'IsComputed'), 0) AS is_computed    ,
CASE      
	WHEN ISTC.CONSTRAINT_TYPE = 'PRIMARY KEY'     
	THEN 1      
	ELSE 0    
END	AS is_primarykey    ,
CASE      
	WHEN ISTC.CONSTRAINT_TYPE = 'UNIQUE'      
	THEN 1      
	ELSE 0    
END	AS is_uniquekey  FROM INFORMATION_SCHEMA.COLUMNS as ISC    
LEFT JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE as ISCCU      
ON ISC.TABLE_SCHEMA = ISCCU.TABLE_SCHEMA        
AND ISC.TABLE_NAME = ISCCU.TABLE_NAME        
AND ISC.COLUMN_NAME = ISCCU.COLUMN_NAME    
LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS ISTC     
ON ISCCU.TABLE_SCHEMA = ISTC.TABLE_SCHEMA       
AND ISCCU.TABLE_NAME = ISTC.TABLE_NAME        
AND ISCCU.CONSTRAINT_NAME = ISTC.CONSTRAINT_NAME  
WHERE ISC.TABLE_SCHEMA = N'dbo'   
AND ISC.TABLE_NAME = N'Tmp_Check' 
ORDER BY ISC.COLUMN_NAME ASC

User's image

Share via

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

2 answers

Your answer