Supress SQL Server GUI view designer extended properites in schema sompare
I'm connected to two Azure SQL Server instances and doing a schema compare of views.
I'm getting a lot of differences between identical views because Datagrip is scripting out sp_addextendedproperty for some of the views. These seem to be saved SQL Server Management Studio layouts for the GUI view designer.
I've checked through the options and none of them are an obvious fit. Is there a way to supress scripting out these and considering them when marking objects as different?
An example:
exec sp_addextendedproperty 'MS_DiagramPane1', N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties =
Begin PaneConfigurations =
Begin PaneConfiguration = 0
NumPanes = 4
Configuration = "(H (1[40] 4[20] 2[20] 3) )"
End
Begin PaneConfiguration = 1
NumPanes = 3
Configuration = "(H (1 [50] 4 [25] 3))"
End
Begin PaneConfiguration = 2
NumPanes = 3
Configuration = "(H (1 [50] 2 [25] 3))"
End
Begin PaneConfiguration = 3
NumPanes = 3
Configuration = "(H (4 [30] 2 [40] 3))"
End
Begin PaneConfiguration = 4
NumPanes = 2
Configuration = "(H (1 [56] 3))"
End
Begin PaneConfiguration = 5
NumPanes = 2
Configuration = "(H (2 [66] 3))"
End
Begin PaneConfiguration = 6
NumPanes = 2
Configuration = "(H (4 [50] 3))"
End
Begin PaneConfiguration = 7
NumPanes = 1
Configuration = "(V (3))"
End
Begin PaneConfiguration = 8
NumPanes = 3
Configuration = "(H (1[56] 4[18] 2) )"
End
Begin PaneConfiguration = 9
NumPanes = 2
Configuration = "(H (1 [75] 4))"
End
Begin PaneConfiguration = 10
NumPanes = 2
Configuration = "(H (1[66] 2) )"
End
Begin PaneConfiguration = 11
NumPanes = 2
Configuration = "(H (4 [60] 2))"
End
Begin PaneConfiguration = 12
NumPanes = 1
Configuration = "(H (1) )"
End
Begin PaneConfiguration = 13
NumPanes = 1
Configuration = "(V (4))"
End
Begin PaneConfiguration = 14
NumPanes = 1
Configuration = "(V (2))"
End
ActivePaneConfig = 0
End
Begin DiagramPane =
Begin Origin =
Top = 0
Left = 0
End
Begin Tables =
Begin Table = "CAC"
Begin Extent =
Top = 7
Left = 48
Bottom = 170
Right = 380
End
DisplayFlags = 280
TopColumn = 23
End
Begin Table = "L"
Begin Extent =
Top = 175
Left = 48
Bottom = 338
Right = 380
End
DisplayFlags = 280
TopColumn = 0
End
Begin Table = "S"
Begin Extent =
Top = 343
Left = 48
Bottom = 506
Right = 252
End
DisplayFlags = 280
TopColumn = 0
End
Begin Table = "YG"
Begin Extent =
Top = 511
Left = 48
Bottom = 630
Right = 242
End
DisplayFlags = 280
TopColumn = 0
End
Begin Table = "B"
Begin Extent =
Top = 630
Left = 48
Bottom = 793
Right = 277
End
DisplayFlags = 280
TopColumn = 0
End
End
End
Begin SQLPane =
End
Begin DataPane =
Begin ParameterDefaults = ""
End
End
Begin CriteriaPane =
Begin ColumnWidths = 11
Column = 1440
Alias = 900
Table = 1176
Output = 720
Append = 1400
NewValue = 1170
SortType = 1356
SortOrder = 1416
GroupBy = 1350
Filter = 1356
Or = 1350
Or = 1350
Or = 1350
End
End
End
', 'SCHEMA', 'dbo', 'VIEW', 'XXXXXXXXXXXXX'
go
Begin DesignProperties =
Begin PaneConfigurations =
Begin PaneConfiguration = 0
NumPanes = 4
Configuration = "(H (1[40] 4[20] 2[20] 3) )"
End
Begin PaneConfiguration = 1
NumPanes = 3
Configuration = "(H (1 [50] 4 [25] 3))"
End
Begin PaneConfiguration = 2
NumPanes = 3
Configuration = "(H (1 [50] 2 [25] 3))"
End
Begin PaneConfiguration = 3
NumPanes = 3
Configuration = "(H (4 [30] 2 [40] 3))"
End
Begin PaneConfiguration = 4
NumPanes = 2
Configuration = "(H (1 [56] 3))"
End
Begin PaneConfiguration = 5
NumPanes = 2
Configuration = "(H (2 [66] 3))"
End
Begin PaneConfiguration = 6
NumPanes = 2
Configuration = "(H (4 [50] 3))"
End
Begin PaneConfiguration = 7
NumPanes = 1
Configuration = "(V (3))"
End
Begin PaneConfiguration = 8
NumPanes = 3
Configuration = "(H (1[56] 4[18] 2) )"
End
Begin PaneConfiguration = 9
NumPanes = 2
Configuration = "(H (1 [75] 4))"
End
Begin PaneConfiguration = 10
NumPanes = 2
Configuration = "(H (1[66] 2) )"
End
Begin PaneConfiguration = 11
NumPanes = 2
Configuration = "(H (4 [60] 2))"
End
Begin PaneConfiguration = 12
NumPanes = 1
Configuration = "(H (1) )"
End
Begin PaneConfiguration = 13
NumPanes = 1
Configuration = "(V (4))"
End
Begin PaneConfiguration = 14
NumPanes = 1
Configuration = "(V (2))"
End
ActivePaneConfig = 0
End
Begin DiagramPane =
Begin Origin =
Top = 0
Left = 0
End
Begin Tables =
Begin Table = "CAC"
Begin Extent =
Top = 7
Left = 48
Bottom = 170
Right = 380
End
DisplayFlags = 280
TopColumn = 23
End
Begin Table = "L"
Begin Extent =
Top = 175
Left = 48
Bottom = 338
Right = 380
End
DisplayFlags = 280
TopColumn = 0
End
Begin Table = "S"
Begin Extent =
Top = 343
Left = 48
Bottom = 506
Right = 252
End
DisplayFlags = 280
TopColumn = 0
End
Begin Table = "YG"
Begin Extent =
Top = 511
Left = 48
Bottom = 630
Right = 242
End
DisplayFlags = 280
TopColumn = 0
End
Begin Table = "B"
Begin Extent =
Top = 630
Left = 48
Bottom = 793
Right = 277
End
DisplayFlags = 280
TopColumn = 0
End
End
End
Begin SQLPane =
End
Begin DataPane =
Begin ParameterDefaults = ""
End
End
Begin CriteriaPane =
Begin ColumnWidths = 11
Column = 1440
Alias = 900
Table = 1176
Output = 720
Append = 1400
NewValue = 1170
SortType = 1356
SortOrder = 1416
GroupBy = 1350
Filter = 1356
Or = 1350
Or = 1350
Or = 1350
End
End
End
', 'SCHEMA', 'dbo', 'VIEW', 'XXXXXXXXXXXXX'
go
Please sign in to leave a comment.
Could you provide sample DDLs for your identical views generated by SQL Generator in DataGrip?
Okay.
I go into SSMS are create a simple view:
Scripting the DDL to concole in DataGrip gives:
All okay.
I then go into SSMS Design view and add a column and some aliases:
Scripting the DDL in SSMS now gives:
Scripting the DDL in DataGrip gives:
SSMS saves the layout of the Design pane as extended properties. However I would never want this scripted out by DataGrip when looking at the DDL in nomrla use. For compare this will cuase the added probelm of showing identical views as being different, because a view was opened in Design on one instance and not the other.
Once you have modified the view through SSMS, please run OBJECT_DEFINITION ( object_id ) for the given object and check its definition returned by the output:
https://learn.microsoft.com/en-us/sql/t-sql/functions/object-definition-transact-sql
If it returns the same DDL as provided in your last example, it's expected that you'll have the exact definition returned by Azure SQL database. We don't perform any DDL preprocessing. For consistency purposes, we'd encourage you to alter the objects DDL in DataGrip.