How can we help you today? How can we help you today?

Can you ignore column order and extended property order

I'm trying to compare two databases and am finding that in some of the tables some of the columns and some of the extended properties were entered in different orders. All of these differences in order, but not in substance, are showing up when I compare the tables. For instance, I have one table where a new extended property was inserted after the first one and now all the extended properties after the first one are flagged as differences.

*****************
For example:
Table 1 has:
EXEC sp_addextendedproperty N'MS_Description', N'Description 1', 'SCHEMA', N'dbo', 'TABLE', N'Table 1', 'COLUMN', N'Column1'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Description 2', 'SCHEMA', N'dbo', 'TABLE', N'Table 1', 'COLUMN', N'Column2'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Description 3', 'SCHEMA', N'dbo', 'TABLE', N'Table 1', 'COLUMN', N'Column3'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Description 4', 'SCHEMA', N'dbo', 'TABLE', N'Table 1', 'COLUMN', N'Column4'
GO
Table 2 has:
EXEC sp_addextendedproperty N'MS_Description', N'Description 1', 'SCHEMA', N'dbo', 'TABLE', N'Table 1', 'COLUMN', N'Column1'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Description 4', 'SCHEMA', N'dbo', 'TABLE', N'Table 1', 'COLUMN', N'Column4'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Description 2', 'SCHEMA', N'dbo', 'TABLE', N'Table 1', 'COLUMN', N'Column2'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Description 3', 'SCHEMA', N'dbo', 'TABLE', N'Table 1', 'COLUMN', N'Column3'
GO
All of the extended properties after the first one are flagged as being different even though it is only their order that is different. I can ignore all the extended properties, but then wouldn't be able to find ones that are missing in one of the tables.
*********************

I understand that internally, SQL Server would consider these as differences since the underlying object ID's might be different, but that is not really relevant to for our purposes, ie making sure that all the required elements (columns and extended properties) exist and have the same properties (datatype, size, description) for both sets of tables.

Is there anyway to ignore these kinds of differences? I haven't found anything in the options so far. I'm using SQL Compare 10.7.
Helen
0

Comments

1 comment

Sort by
  • Evan Moss
    Hello,

    Thanks for your post.

    I just tried reproducing your behavior and wasn't able to.

    I created two databases, a and b, and ran these scripts on them:
    CREATE TABLE [dbo].[table 1]
(Column1 INT,
Column2 INT,
Column3 INT,
Column4 INT)
GO
EXEC sp_addextendedproperty N'MS_Description', N'Description 1', 'SCHEMA', N'dbo', 'TABLE', N'Table 1', 'COLUMN', N'Column1'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Description 2', 'SCHEMA', N'dbo', 'TABLE', N'Table 1', 'COLUMN', N'Column2'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Description 3', 'SCHEMA', N'dbo', 'TABLE', N'Table 1', 'COLUMN', N'Column3'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Description 4', 'SCHEMA', N'dbo', 'TABLE', N'Table 1', 'COLUMN', N'Column4'
GO

    CREATE TABLE [dbo].[table 1]
(Column1 INT,
Column2 INT,
Column3 INT,
Column4 INT)
GO
EXEC sp_addextendedproperty N'MS_Description', N'Description 1', 'SCHEMA', N'dbo', 'TABLE', N'Table 1', 'COLUMN', N'Column1'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Description 4', 'SCHEMA', N'dbo', 'TABLE', N'Table 1', 'COLUMN', N'Column4'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Description 2', 'SCHEMA', N'dbo', 'TABLE', N'Table 1', 'COLUMN', N'Column2'
GO
EXEC sp_addextendedproperty N'MS_Description', N'Description 3', 'SCHEMA', N'dbo', 'TABLE', N'Table 1', 'COLUMN', N'Column3'
GO

    Then I compared them in SQL Compare 10.7 and they did not show up as differences.

    Is there anything else you're doing that could explain different behavior? Are you using the default comparison options?

    Thanks,
    Evan
    Evan Moss
    0

Add comment

Please sign in to leave a comment.