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
0
3 comments

Could you provide sample DDLs for your identical views generated by SQL Generator in DataGrip?

0

Okay.

I go into SSMS are create a simple view:

create   view [dbo].[TestDelete]
as
select    [NameEnglish]
from    [dbo].[Band]

Scripting the DDL to concole in DataGrip gives:

create   view [dbo].[TestDelete]
as
select    [NameEnglish]
from    [dbo].[Band]
GO

All okay.

I then go into SSMS Design view and add a column and some aliases:

Scripting the DDL in SSMS now gives:

CREATE VIEW [dbo].[TestDelete]
AS
SELECT NameEnglish AS Name, BandId AS Id
FROM     dbo.Band
GO

Scripting the DDL in DataGrip gives:

CREATE VIEW dbo.TestDelete
AS
SELECT NameEnglish AS Name, BandId AS Id
FROM     dbo.Band
go

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 = "Band (dbo)"
            Begin Extent =
               Top = 7
               Left = 48
               Bottom = 325
               Right = 709
            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 = 1170
         Output = 720
         Append = 1400
         NewValue = 1170
         SortType = 1350
         SortOrder = 1410
         GroupBy = 1350
         Filter = 1350
         Or = 1350
         Or = 1350
         Or = 1350
      End
   End
End
', 'SCHEMA', 'dbo', 'VIEW', 'TestDelete'
go

exec sp_addextendedproperty 'MS_DiagramPaneCount', 1, 'SCHEMA', 'dbo', 'VIEW', 'TestDelete'
go

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.

 

0
Hi Sam,

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.
0

Please sign in to leave a comment.