Гость
Войти | Профиль | Очистить
Действия ...
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Помогите написать запрос / 17 сообщений из 17, страница 1 из 1
24.08.2020, 05:58
    #39991862
Roust_m
Roust_m
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите написать запрос
Добрый день,

У меня есть таблица, в которой есть записи по опциям баз в разных средах, столбец [Environment] (production, development). Базы разбиты на группы ([DBGroupID]). Ниже запрос из этой таблицы который показывает ее структуру.

Мне нужно написать запрос, который покажет опции, которые отличаются для одной и той же базы (столбец [name]) из одной группы. Что-то типа:

Код: sql
1.
2.
3.
4.
DBGroupID Name Option Production Development
5      MyDB1 is_broker_enabled 1 0
5      MyDB2 is_broker_enabled 1 0
5      MyDB3 snapshot_isolation_state 1 0




Спасибо.


 
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
SELECT [DBGroupID]
      ,[Environment]
      ,[name]
      ,[owner_sid]
      ,[compatibility_level]
      ,[collation_name]
      ,[user_access_desc]
      ,[is_read_only]
      ,[is_auto_close_on]
      ,[is_auto_shrink_on]
      ,[state_desc]
      ,[is_in_standby]
      ,[is_cleanly_shutdown]
      ,[is_supplemental_logging_enabled]
      ,[snapshot_isolation_state]
      ,[is_read_committed_snapshot_on]
      ,[recovery_model_desc]
      ,[page_verify_option_desc]
      ,[is_auto_create_stats_on]
      ,[is_auto_create_stats_incremental_on]
      ,[is_auto_update_stats_on]
      ,[is_auto_update_stats_async_on]
      ,[is_ansi_null_default_on]
      ,[is_ansi_nulls_on]
      ,[is_ansi_padding_on]
      ,[is_ansi_warnings_on]
      ,[is_arithabort_on]
      ,[is_concat_null_yields_null_on]
      ,[is_numeric_roundabort_on]
      ,[is_quoted_identifier_on]
      ,[is_recursive_triggers_on]
      ,[is_cursor_close_on_commit_on]
      ,[is_local_cursor_default]
      ,[is_fulltext_enabled]
      ,[is_trustworthy_on]
      ,[is_db_chaining_on]
      ,[is_parameterization_forced]
      ,[is_master_key_encrypted_by_server]
      ,[is_query_store_on]
      ,[is_published]
      ,[is_subscribed]
      ,[is_merge_published]
      ,[is_distributor]
      ,[is_sync_with_backup]
      ,[is_broker_enabled]
      ,[log_reuse_wait_desc]
      ,[is_date_correlation_on]
      ,[is_cdc_enabled]
      ,[is_encrypted]
      ,[is_honor_broker_priority_on]
      ,[default_language_name]
      ,[default_fulltext_language_name]
      ,[is_nested_triggers_on]
      ,[is_transform_noise_words_on]
      ,[two_digit_year_cutoff]
      ,[containment_desc]
      ,[target_recovery_time_in_seconds]
      ,[delayed_durability_desc]
      ,[is_memory_optimized_elevate_to_snapshot_on]
      ,[is_federation_member]
      ,[is_remote_data_archive_enabled]
      ,[is_mixed_page_allocation_on]
  FROM [MyDB].[dbo].[DBProperties]

...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
24.08.2020, 07:53
    #39991867
Akina
Akina
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите написать запрос
Схематично - например так:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
SELECT t1.DBGroupID, t1.Name, t1.Option, t1.value forProduction, t2.value forDevelopment
FROM table t1
JOIN table t2 ON t1.DBGroupID = t2.DBGroupID
             AND t1.Name = t2.Name
             AND t1.Option = t2.Option
WHERE t1.Environment = 'production'
  AND t2.Environment = 'development'
  AND t1.value != t2.value
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
24.08.2020, 08:21
    #39991870
Roust_m
Roust_m
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите написать запрос
Akina
Схематично - например так:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
SELECT t1.DBGroupID, t1.Name, t1.Option, t1.value forProduction, t2.value forDevelopment
FROM table t1
JOIN table t2 ON t1.DBGroupID = t2.DBGroupID
             AND t1.Name = t2.Name
             AND t1.Option = t2.Option
WHERE t1.Environment = 'production'
  AND t2.Environment = 'development'
  AND t1.value != t2.value



Такой запрос придется писать для каждой опции, которых десятки. Хотелось что-то более компактное. Я надеялся, что через Pivot что-то получится, сам не могу сообразить как написать.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
24.08.2020, 08:45
    #39991871
uaggster
uaggster
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите написать запрос
 
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
;With t
as
(
	SELECT t.*
	FROM        [MyDB].[dbo].[DBProperties]
	Cross apply (
		Values ( [DBGroupID], '[Environment]',                                [Environment]                                )
		,      ( [DBGroupID], '[name]',                                       [name]                                       )
		,      ( [DBGroupID], '[owner_sid]',                                  [owner_sid]                                  )
		,      ( [DBGroupID], '[compatibility_level]',                        [compatibility_level]                        )
		,      ( [DBGroupID], '[collation_name]',                             [collation_name]                             )
		,      ( [DBGroupID], '[user_access_desc]',                           [user_access_desc]                           )
		,      ( [DBGroupID], '[is_read_only]',                               [is_read_only]                               )
		,      ( [DBGroupID], '[is_auto_close_on]',                           [is_auto_close_on]                           )
		,      ( [DBGroupID], '[is_auto_shrink_on]',                          [is_auto_shrink_on]                          )
		,      ( [DBGroupID], '[state_desc]',                                 [state_desc]                                 )
		,      ( [DBGroupID], '[is_in_standby]',                              [is_in_standby]                              )
		,      ( [DBGroupID], '[is_cleanly_shutdown]',                        [is_cleanly_shutdown]                        )
		,      ( [DBGroupID], '[is_supplemental_logging_enabled]',            [is_supplemental_logging_enabled]            )
		,      ( [DBGroupID], '[snapshot_isolation_state]',                   [snapshot_isolation_state]                   )
		,      ( [DBGroupID], '[is_read_committed_snapshot_on]',              [is_read_committed_snapshot_on]              )
		,      ( [DBGroupID], '[recovery_model_desc]',                        [recovery_model_desc]                        )
		,      ( [DBGroupID], '[page_verify_option_desc]',                    [page_verify_option_desc]                    )
		,      ( [DBGroupID], '[is_auto_create_stats_on]',                    [is_auto_create_stats_on]                    )
		,      ( [DBGroupID], '[is_auto_create_stats_incremental_on]',        [is_auto_create_stats_incremental_on]        )
		,      ( [DBGroupID], '[is_auto_update_stats_on]',                    [is_auto_update_stats_on]                    )
		,      ( [DBGroupID], '[is_auto_update_stats_async_on]',              [is_auto_update_stats_async_on]              )
		,      ( [DBGroupID], '[is_ansi_null_default_on]',                    [is_ansi_null_default_on]                    )
		,      ( [DBGroupID], '[is_ansi_nulls_on]',                           [is_ansi_nulls_on]                           )
		,      ( [DBGroupID], '[is_ansi_padding_on]',                         [is_ansi_padding_on]                         )
		,      ( [DBGroupID], '[is_ansi_warnings_on]',                        [is_ansi_warnings_on]                        )
		,      ( [DBGroupID], '[is_arithabort_on]',                           [is_arithabort_on]                           )
		,      ( [DBGroupID], '[is_concat_null_yields_null_on]',              [is_concat_null_yields_null_on]              )
		,      ( [DBGroupID], '[is_numeric_roundabort_on]',                   [is_numeric_roundabort_on]                   )
		,      ( [DBGroupID], '[is_quoted_identifier_on]',                    [is_quoted_identifier_on]                    )
		,      ( [DBGroupID], '[is_recursive_triggers_on]',                   [is_recursive_triggers_on]                   )
		,      ( [DBGroupID], '[is_cursor_close_on_commit_on]',               [is_cursor_close_on_commit_on]               )
		,      ( [DBGroupID], '[is_local_cursor_default]',                    [is_local_cursor_default]                    )
		,      ( [DBGroupID], '[is_fulltext_enabled]',                        [is_fulltext_enabled]                        )
		,      ( [DBGroupID], '[is_trustworthy_on]',                          [is_trustworthy_on]                          )
		,      ( [DBGroupID], '[is_db_chaining_on]',                          [is_db_chaining_on]                          )
		,      ( [DBGroupID], '[is_parameterization_forced]',                 [is_parameterization_forced]                 )
		,      ( [DBGroupID], '[is_master_key_encrypted_by_server]',          [is_master_key_encrypted_by_server]          )
		,      ( [DBGroupID], '[is_query_store_on]',                          [is_query_store_on]                          )
		,      ( [DBGroupID], '[is_published]',                               [is_published]                               )
		,      ( [DBGroupID], '[is_subscribed]',                              [is_subscribed]                              )
		,      ( [DBGroupID], '[is_merge_published]',                         [is_merge_published]                         )
		,      ( [DBGroupID], '[is_distributor]',                             [is_distributor]                             )
		,      ( [DBGroupID], '[is_sync_with_backup]',                        [is_sync_with_backup]                        )
		,      ( [DBGroupID], '[is_broker_enabled]',                          [is_broker_enabled]                          )
		,      ( [DBGroupID], '[log_reuse_wait_desc]',                        [log_reuse_wait_desc]                        )
		,      ( [DBGroupID], '[is_date_correlation_on]',                     [is_date_correlation_on]                     )
		,      ( [DBGroupID], '[is_cdc_enabled]',                             [is_cdc_enabled]                             )
		,      ( [DBGroupID], '[is_encrypted]',                               [is_encrypted]                               )
		,      ( [DBGroupID], '[is_honor_broker_priority_on]',                [is_honor_broker_priority_on]                )
		,      ( [DBGroupID], '[default_language_name]',                      [default_language_name]                      )
		,      ( [DBGroupID], '[default_fulltext_language_name]',             [default_fulltext_language_name]             )
		,      ( [DBGroupID], '[is_nested_triggers_on]',                      [is_nested_triggers_on]                      )
		,      ( [DBGroupID], '[is_transform_noise_words_on]',                [is_transform_noise_words_on]                )
		,      ( [DBGroupID], '[two_digit_year_cutoff]',                      [two_digit_year_cutoff]                      )
		,      ( [DBGroupID], '[containment_desc]',                           [containment_desc]                           )
		,      ( [DBGroupID], '[target_recovery_time_in_seconds]',            [target_recovery_time_in_seconds]            )
		,      ( [DBGroupID], '[delayed_durability_desc]',                    [delayed_durability_desc]                    )
		,      ( [DBGroupID], '[is_memory_optimized_elevate_to_snapshot_on]', [is_memory_optimized_elevate_to_snapshot_on] )
		,      ( [DBGroupID], '[is_federation_member]',                       [is_federation_member]                       )
		,      ( [DBGroupID], '[is_remote_data_archive_enabled]',             [is_remote_data_archive_enabled]             )
		,      ( [DBGroupID], '[is_mixed_page_allocation_on]',                [is_mixed_page_allocation_on]                )
		)                                   t(id, name, value)
)
Select *
from t t1
Where not Exists (Select t1.name
	,                    t1.value
	intersect
	select t2.name
	,      t2.value
	from t t2
	where t1.id<>t2.id)

...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
24.08.2020, 08:57
    #39991874
Roust_m
Roust_m
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите написать запрос
 
uaggster
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
;With t
as
(
	SELECT t.*
	FROM        [MyDB].[dbo].[DBProperties]
	Cross apply (
		Values ( [DBGroupID], '[Environment]',                                [Environment]                                )
		,      ( [DBGroupID], '[name]',                                       [name]                                       )
		,      ( [DBGroupID], '[owner_sid]',                                  [owner_sid]                                  )
		,      ( [DBGroupID], '[compatibility_level]',                        [compatibility_level]                        )
		,      ( [DBGroupID], '[collation_name]',                             [collation_name]                             )
		,      ( [DBGroupID], '[user_access_desc]',                           [user_access_desc]                           )
		,      ( [DBGroupID], '[is_read_only]',                               [is_read_only]                               )
		,      ( [DBGroupID], '[is_auto_close_on]',                           [is_auto_close_on]                           )
		,      ( [DBGroupID], '[is_auto_shrink_on]',                          [is_auto_shrink_on]                          )
		,      ( [DBGroupID], '[state_desc]',                                 [state_desc]                                 )
		,      ( [DBGroupID], '[is_in_standby]',                              [is_in_standby]                              )
		,      ( [DBGroupID], '[is_cleanly_shutdown]',                        [is_cleanly_shutdown]                        )
		,      ( [DBGroupID], '[is_supplemental_logging_enabled]',            [is_supplemental_logging_enabled]            )
		,      ( [DBGroupID], '[snapshot_isolation_state]',                   [snapshot_isolation_state]                   )
		,      ( [DBGroupID], '[is_read_committed_snapshot_on]',              [is_read_committed_snapshot_on]              )
		,      ( [DBGroupID], '[recovery_model_desc]',                        [recovery_model_desc]                        )
		,      ( [DBGroupID], '[page_verify_option_desc]',                    [page_verify_option_desc]                    )
		,      ( [DBGroupID], '[is_auto_create_stats_on]',                    [is_auto_create_stats_on]                    )
		,      ( [DBGroupID], '[is_auto_create_stats_incremental_on]',        [is_auto_create_stats_incremental_on]        )
		,      ( [DBGroupID], '[is_auto_update_stats_on]',                    [is_auto_update_stats_on]                    )
		,      ( [DBGroupID], '[is_auto_update_stats_async_on]',              [is_auto_update_stats_async_on]              )
		,      ( [DBGroupID], '[is_ansi_null_default_on]',                    [is_ansi_null_default_on]                    )
		,      ( [DBGroupID], '[is_ansi_nulls_on]',                           [is_ansi_nulls_on]                           )
		,      ( [DBGroupID], '[is_ansi_padding_on]',                         [is_ansi_padding_on]                         )
		,      ( [DBGroupID], '[is_ansi_warnings_on]',                        [is_ansi_warnings_on]                        )
		,      ( [DBGroupID], '[is_arithabort_on]',                           [is_arithabort_on]                           )
		,      ( [DBGroupID], '[is_concat_null_yields_null_on]',              [is_concat_null_yields_null_on]              )
		,      ( [DBGroupID], '[is_numeric_roundabort_on]',                   [is_numeric_roundabort_on]                   )
		,      ( [DBGroupID], '[is_quoted_identifier_on]',                    [is_quoted_identifier_on]                    )
		,      ( [DBGroupID], '[is_recursive_triggers_on]',                   [is_recursive_triggers_on]                   )
		,      ( [DBGroupID], '[is_cursor_close_on_commit_on]',               [is_cursor_close_on_commit_on]               )
		,      ( [DBGroupID], '[is_local_cursor_default]',                    [is_local_cursor_default]                    )
		,      ( [DBGroupID], '[is_fulltext_enabled]',                        [is_fulltext_enabled]                        )
		,      ( [DBGroupID], '[is_trustworthy_on]',                          [is_trustworthy_on]                          )
		,      ( [DBGroupID], '[is_db_chaining_on]',                          [is_db_chaining_on]                          )
		,      ( [DBGroupID], '[is_parameterization_forced]',                 [is_parameterization_forced]                 )
		,      ( [DBGroupID], '[is_master_key_encrypted_by_server]',          [is_master_key_encrypted_by_server]          )
		,      ( [DBGroupID], '[is_query_store_on]',                          [is_query_store_on]                          )
		,      ( [DBGroupID], '[is_published]',                               [is_published]                               )
		,      ( [DBGroupID], '[is_subscribed]',                              [is_subscribed]                              )
		,      ( [DBGroupID], '[is_merge_published]',                         [is_merge_published]                         )
		,      ( [DBGroupID], '[is_distributor]',                             [is_distributor]                             )
		,      ( [DBGroupID], '[is_sync_with_backup]',                        [is_sync_with_backup]                        )
		,      ( [DBGroupID], '[is_broker_enabled]',                          [is_broker_enabled]                          )
		,      ( [DBGroupID], '[log_reuse_wait_desc]',                        [log_reuse_wait_desc]                        )
		,      ( [DBGroupID], '[is_date_correlation_on]',                     [is_date_correlation_on]                     )
		,      ( [DBGroupID], '[is_cdc_enabled]',                             [is_cdc_enabled]                             )
		,      ( [DBGroupID], '[is_encrypted]',                               [is_encrypted]                               )
		,      ( [DBGroupID], '[is_honor_broker_priority_on]',                [is_honor_broker_priority_on]                )
		,      ( [DBGroupID], '[default_language_name]',                      [default_language_name]                      )
		,      ( [DBGroupID], '[default_fulltext_language_name]',             [default_fulltext_language_name]             )
		,      ( [DBGroupID], '[is_nested_triggers_on]',                      [is_nested_triggers_on]                      )
		,      ( [DBGroupID], '[is_transform_noise_words_on]',                [is_transform_noise_words_on]                )
		,      ( [DBGroupID], '[two_digit_year_cutoff]',                      [two_digit_year_cutoff]                      )
		,      ( [DBGroupID], '[containment_desc]',                           [containment_desc]                           )
		,      ( [DBGroupID], '[target_recovery_time_in_seconds]',            [target_recovery_time_in_seconds]            )
		,      ( [DBGroupID], '[delayed_durability_desc]',                    [delayed_durability_desc]                    )
		,      ( [DBGroupID], '[is_memory_optimized_elevate_to_snapshot_on]', [is_memory_optimized_elevate_to_snapshot_on] )
		,      ( [DBGroupID], '[is_federation_member]',                       [is_federation_member]                       )
		,      ( [DBGroupID], '[is_remote_data_archive_enabled]',             [is_remote_data_archive_enabled]             )
		,      ( [DBGroupID], '[is_mixed_page_allocation_on]',                [is_mixed_page_allocation_on]                )
		)                                   t(id, name, value)
)
Select *
from t t1
Where not Exists (Select t1.name
	,                    t1.value
	intersect
	select t2.name
	,      t2.value
	from t t2
	where t1.id<>t2.id)



Выдает ошибку:
Conversion failed when converting the nvarchar value 'Production' to data type tinyint.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
24.08.2020, 09:12
    #39991880
uaggster
uaggster
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите написать запрос
Roust_m, а так?
Код: sql
1.
		Values ( [DBGroupID], '[Environment]',                                Cast([Environment] as sql_variant)           )
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
24.08.2020, 09:20
    #39991883
Roust_m
Roust_m
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите написать запрос
uaggster
Roust_m, а так?
Код: sql
1.
		Values ( [DBGroupID], '[Environment]',                                Cast([Environment] as sql_variant)           )



А так работает без ошибок,но показывает все опции, также мне помимо номера группы, нужно еще имя базы а также очень желательно значения опции для обеих сред, что-то типа:

Код: sql
1.
2.
3.
4.
DBGroupID Name Option Production Development
5      MyDB1 is_broker_enabled 1 0
5      MyDB2 is_broker_enabled 1 0
5      MyDB3 snapshot_isolation_state 1 0
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
24.08.2020, 09:41
    #39991889
Akina
Akina
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите написать запрос
Roust_m
Такой запрос придется писать для каждой опции, которых десятки. Хотелось что-то более компактное.
То есть Вы хотите все поля сравнить, и для каждой несовпадающей вывести несоответствия? так это наоборот - надо распивотить в EAV, одно поле на запись, а потом отдельные поля сравнивать.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
24.08.2020, 09:42
    #39991890
Ennor Tiegael
Ennor Tiegael
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите написать запрос
Можно через XML сделать. Ну, если у вас там не десятки сотен тысяч записей:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
declare @t table (
	Name sysname,
	Option1 bit not null,
	Option2 sysname not null,
	Option3 int null
);

insert into @t (Name, Option1, Option2, Option3)
values
	('DB1', 1, 'GBOiwhe9uwn', 45),
	('DB2', 1, 'Value', 11),
	('DB3', 1, 'Value', 45),
	('DB4', 0, 'Value', 45),
	('DB5', 1, 'Value', 45);

declare @x xml = (
	select cast(x.d as xml) as [X] from (
		select d.* from @t d for xml auto
	) x(d)
);

select d.c.value('./@Name', 'sysname') as [DbName],
	a.v.value('local-name(.)', 'sysname') as [Property],
	a.v.value('.', 'nvarchar(max)') as [Value]
from @x.nodes('/d') d(c)
	cross apply d.c.nodes('@*') a(v);

Динамический unpivot эта штука делает, а дальше все зависит от вашего определенияавторопции, которые отличаются для одной и той же базыИмхо, тут либо у меня русский язык сбоит, либо у вас...
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
24.08.2020, 09:43
    #39991891
Roust_m
Roust_m
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите написать запрос
Я также не совсем понимаю этого фильтра:
Код: sql
1.
2.
3.
4.
5.
6.
7.
Where not Exists (Select t1.name
	,                    t1.value
	intersect
	select t2.name
	,      t2.value
	from t t2
	where t1.id<>t2.id)


Набор опций для одной и тоже же базы из одной и той же группы в разных средах (Production, Development) одинаковый. Значения опций разные. Допустим в Production is_broker_enabled равен 1 а в Development равен 0.

Я немножко запрос подкрутил, теперь он мне выдает почти то что мне нужно, осталось сравнение сделать и по средам строки в стобцы преобразовать:
 
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
;With t
as
(
	SELECT t.*
	FROM        [dbo].[DBProperties]
	Cross apply (
		Values ( [DBGroupID], [name], [Environment], '[owner_sid]',                                  [owner_sid]                                  )
		,      ( [DBGroupID], [name], [Environment], '[compatibility_level]',                        [compatibility_level]                        )
		,      ( [DBGroupID], [name], [Environment], '[collation_name]',                             Cast([collation_name] as sql_variant)       )
		,      ( [DBGroupID], [name], [Environment], '[user_access_desc]',                           [user_access_desc]                           )
		,      ( [DBGroupID], [name], [Environment], '[is_read_only]',                               [is_read_only]                               )
		,      ( [DBGroupID], [name], [Environment], '[is_auto_close_on]',                           [is_auto_close_on]                           )
		,      ( [DBGroupID], [name], [Environment], '[is_auto_shrink_on]',                          [is_auto_shrink_on]                          )
		,      ( [DBGroupID], [name], [Environment], '[state_desc]',                                 [state_desc]                                 )
		,      ( [DBGroupID], [name], [Environment], '[is_in_standby]',                              [is_in_standby]                              )
		,      ( [DBGroupID], [name], [Environment], '[is_cleanly_shutdown]',                        [is_cleanly_shutdown]                        )
		,      ( [DBGroupID], [name], [Environment], '[is_supplemental_logging_enabled]',            [is_supplemental_logging_enabled]            )
		,      ( [DBGroupID], [name], [Environment], '[snapshot_isolation_state_desc]',                   [snapshot_isolation_state_desc]                   )
		,      ( [DBGroupID], [name], [Environment], '[is_read_committed_snapshot_on]',              [is_read_committed_snapshot_on]              )
		,      ( [DBGroupID], [name], [Environment], '[recovery_model_desc]',                        [recovery_model_desc]                        )
		,      ( [DBGroupID], [name], [Environment], '[page_verify_option_desc]',                    [page_verify_option_desc]                    )
		,      ( [DBGroupID], [name], [Environment], '[is_auto_create_stats_on]',                    [is_auto_create_stats_on]                    )
		,      ( [DBGroupID], [name], [Environment], '[is_auto_create_stats_incremental_on]',        [is_auto_create_stats_incremental_on]        )
		,      ( [DBGroupID], [name], [Environment], '[is_auto_update_stats_on]',                    [is_auto_update_stats_on]                    )
		,      ( [DBGroupID], [name], [Environment], '[is_auto_update_stats_async_on]',              [is_auto_update_stats_async_on]              )
		,      ( [DBGroupID], [name], [Environment], '[is_ansi_null_default_on]',                    [is_ansi_null_default_on]                    )
		,      ( [DBGroupID], [name], [Environment], '[is_ansi_nulls_on]',                           [is_ansi_nulls_on]                           )
		,      ( [DBGroupID], [name], [Environment], '[is_ansi_padding_on]',                         [is_ansi_padding_on]                         )
		,      ( [DBGroupID], [name], [Environment], '[is_ansi_warnings_on]',                        [is_ansi_warnings_on]                        )
		,      ( [DBGroupID], [name], [Environment], '[is_arithabort_on]',                           [is_arithabort_on]                           )
		,      ( [DBGroupID], [name], [Environment], '[is_concat_null_yields_null_on]',              [is_concat_null_yields_null_on]              )
		,      ( [DBGroupID], [name], [Environment], '[is_numeric_roundabort_on]',                   [is_numeric_roundabort_on]                   )
		,      ( [DBGroupID], [name], [Environment], '[is_quoted_identifier_on]',                    [is_quoted_identifier_on]                    )
		,      ( [DBGroupID], [name], [Environment], '[is_recursive_triggers_on]',                   [is_recursive_triggers_on]                   )
		,      ( [DBGroupID], [name], [Environment], '[is_cursor_close_on_commit_on]',               [is_cursor_close_on_commit_on]               )
		,      ( [DBGroupID], [name], [Environment], '[is_local_cursor_default]',                    [is_local_cursor_default]                    )
		,      ( [DBGroupID], [name], [Environment], '[is_fulltext_enabled]',                        [is_fulltext_enabled]                        )
		,      ( [DBGroupID], [name], [Environment], '[is_trustworthy_on]',                          [is_trustworthy_on]                          )
		,      ( [DBGroupID], [name], [Environment], '[is_db_chaining_on]',                          [is_db_chaining_on]                          )
		,      ( [DBGroupID], [name], [Environment], '[is_parameterization_forced]',                 [is_parameterization_forced]                 )
		,      ( [DBGroupID], [name], [Environment], '[is_master_key_encrypted_by_server]',          [is_master_key_encrypted_by_server]          )
		,      ( [DBGroupID], [name], [Environment], '[is_query_store_on]',                          [is_query_store_on]                          )
		,      ( [DBGroupID], [name], [Environment], '[is_published]',                               [is_published]                               )
		,      ( [DBGroupID], [name], [Environment], '[is_subscribed]',                              [is_subscribed]                              )
		,      ( [DBGroupID], [name], [Environment], '[is_merge_published]',                         [is_merge_published]                         )
		,      ( [DBGroupID], [name], [Environment], '[is_distributor]',                             [is_distributor]                             )
		,      ( [DBGroupID], [name], [Environment], '[is_sync_with_backup]',                        [is_sync_with_backup]                        )
		,      ( [DBGroupID], [name], [Environment], '[is_broker_enabled]',                          [is_broker_enabled]                          )
		,      ( [DBGroupID], [name], [Environment], '[log_reuse_wait_desc]',                        [log_reuse_wait_desc]                        )
		,      ( [DBGroupID], [name], [Environment], '[is_date_correlation_on]',                     [is_date_correlation_on]                     )
		,      ( [DBGroupID], [name], [Environment], '[is_cdc_enabled]',                             [is_cdc_enabled]                             )
		,      ( [DBGroupID], [name], [Environment], '[is_encrypted]',                               [is_encrypted]                               )
		,      ( [DBGroupID], [name], [Environment], '[is_honor_broker_priority_on]',                [is_honor_broker_priority_on]                )
		,      ( [DBGroupID], [name], [Environment], '[default_language_name]',                      [default_language_name]                      )
		,      ( [DBGroupID], [name], [Environment], '[default_fulltext_language_name]',             [default_fulltext_language_name]             )
		,      ( [DBGroupID], [name], [Environment], '[is_nested_triggers_on]',                      [is_nested_triggers_on]                      )
		,      ( [DBGroupID], [name], [Environment], '[is_transform_noise_words_on]',                [is_transform_noise_words_on]                )
		,      ( [DBGroupID], [name], [Environment], '[two_digit_year_cutoff]',                      [two_digit_year_cutoff]                      )
		,      ( [DBGroupID], [name], [Environment], '[containment_desc]',                           [containment_desc]                           )
		,      ( [DBGroupID], [name], [Environment], '[target_recovery_time_in_seconds]',            [target_recovery_time_in_seconds]            )
		,      ( [DBGroupID], [name], [Environment], '[delayed_durability_desc]',                    [delayed_durability_desc]                    )
		,      ( [DBGroupID], [name], [Environment], '[is_memory_optimized_elevate_to_snapshot_on]', [is_memory_optimized_elevate_to_snapshot_on] )
		,      ( [DBGroupID], [name], [Environment], '[is_federation_member]',                       [is_federation_member]                       )
		,      ( [DBGroupID], [name], [Environment], '[is_remote_data_archive_enabled]',             [is_remote_data_archive_enabled]             )
		,      ( [DBGroupID], [name], [Environment], '[is_mixed_page_allocation_on]',                [is_mixed_page_allocation_on]                )
		)                                   t(id, name, Environment, OptionName, Optionvalue)
)
Select *
from t t1
--Where not Exists (Select t1.OptionName
--	,                    t1.Optionvalue
--	intersect
--	select t2.OptionName
--	,      t2.Optionvalue
--	from t t2
--	where t1.id<>t2.id)
order by name, OptionName, Environment

...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
24.08.2020, 10:11
    #39991899
Roust_m
Roust_m
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите написать запрос
Подкрутил нижнюю часть запроса, и теперь результат который мне нужен:
Код: sql
1.
2.
3.
4.
5.
6.
7.
...
Select t1.DBGroupID, t1.DBName, t1.OptionName, t1.Optionvalue Production, t2.Optionvalue Development
from t t1
join t t2
on t1.DBGroupID = t2.DBGroupID and t1.DBName = t2.DBName and t1.OptionName = t2.OptionName 
where  t1.Environment = 'Production' and t2.Environment = 'Development' and t1.Optionvalue <> t2.Optionvalue
order by t1.DBName, t1.OptionName
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
24.08.2020, 10:32
    #39991910
Roust_m
Roust_m
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите написать запрос
Akina
Roust_m
Такой запрос придется писать для каждой опции, которых десятки. Хотелось что-то более компактное.
То есть Вы хотите все поля сравнить, и для каждой несовпадающей вывести несоответствия? так это наоборот - надо распивотить в EAV, одно поле на запись, а потом отдельные поля сравнивать.


Совершенно верно, не PIVOT а UNPIVOT. Я до этого неправильно написал.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
24.08.2020, 10:38
    #39991912
Roust_m
Roust_m
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите написать запрос
Ennor Tiegael
Можно через XML сделать. Ну, если у вас там не десятки сотен тысяч записей:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
declare @t table (
	Name sysname,
	Option1 bit not null,
	Option2 sysname not null,
	Option3 int null
);

insert into @t (Name, Option1, Option2, Option3)
values
	('DB1', 1, 'GBOiwhe9uwn', 45),
	('DB2', 1, 'Value', 11),
	('DB3', 1, 'Value', 45),
	('DB4', 0, 'Value', 45),
	('DB5', 1, 'Value', 45);

declare @x xml = (
	select cast(x.d as xml) as [X] from (
		select d.* from @t d for xml auto
	) x(d)
);

select d.c.value('./@Name', 'sysname') as [DbName],
	a.v.value('local-name(.)', 'sysname') as [Property],
	a.v.value('.', 'nvarchar(max)') as [Value]
from @x.nodes('/d') d(c)
	cross apply d.c.nodes('@*') a(v);

Динамический unpivot эта штука делает, а дальше все зависит от вашего определенияавторопции, которые отличаются для одной и той же базы
Имхо, тут либо у меня русский язык сбоит, либо у вас...

Я не совсем понимаю как это работает, но завтра попробую.

Мне нужно столбцы [DBGroupID], [name], [Environment] оставить, а остальные unpivot, причем один unpivot для production а другой для development. Запрос ниже это делает, вот только он немного громоздкий и зависит от числа столбцов (опций), которые отличаются в разных версиях сиквела. Хотелось бы сделать это независимым.

 
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
;With t
as
(
	SELECT t.*
	FROM        [dbo].[DBProperties]
	Cross apply (
		Values ( [DBGroupID], [name], [Environment], '[owner_sid]',                                  [owner_sid]                                  )
		,      ( [DBGroupID], [name], [Environment], '[compatibility_level]',                        [compatibility_level]                        )
		,      ( [DBGroupID], [name], [Environment], '[collation_name]',                             Cast([collation_name] as sql_variant)       )
		,      ( [DBGroupID], [name], [Environment], '[user_access_desc]',                           [user_access_desc]                           )
		,      ( [DBGroupID], [name], [Environment], '[is_read_only]',                               [is_read_only]                               )
		,      ( [DBGroupID], [name], [Environment], '[is_auto_close_on]',                           [is_auto_close_on]                           )
		,      ( [DBGroupID], [name], [Environment], '[is_auto_shrink_on]',                          [is_auto_shrink_on]                          )
		,      ( [DBGroupID], [name], [Environment], '[state_desc]',                                 [state_desc]                                 )
		,      ( [DBGroupID], [name], [Environment], '[is_in_standby]',                              [is_in_standby]                              )
		,      ( [DBGroupID], [name], [Environment], '[is_cleanly_shutdown]',                        [is_cleanly_shutdown]                        )
		,      ( [DBGroupID], [name], [Environment], '[is_supplemental_logging_enabled]',            [is_supplemental_logging_enabled]            )
		,      ( [DBGroupID], [name], [Environment], '[snapshot_isolation_state_desc]',                   [snapshot_isolation_state_desc]                   )
		,      ( [DBGroupID], [name], [Environment], '[is_read_committed_snapshot_on]',              [is_read_committed_snapshot_on]              )
		,      ( [DBGroupID], [name], [Environment], '[recovery_model_desc]',                        [recovery_model_desc]                        )
		,      ( [DBGroupID], [name], [Environment], '[page_verify_option_desc]',                    [page_verify_option_desc]                    )
		,      ( [DBGroupID], [name], [Environment], '[is_auto_create_stats_on]',                    [is_auto_create_stats_on]                    )
		,      ( [DBGroupID], [name], [Environment], '[is_auto_create_stats_incremental_on]',        [is_auto_create_stats_incremental_on]        )
		,      ( [DBGroupID], [name], [Environment], '[is_auto_update_stats_on]',                    [is_auto_update_stats_on]                    )
		,      ( [DBGroupID], [name], [Environment], '[is_auto_update_stats_async_on]',              [is_auto_update_stats_async_on]              )
		,      ( [DBGroupID], [name], [Environment], '[is_ansi_null_default_on]',                    [is_ansi_null_default_on]                    )
		,      ( [DBGroupID], [name], [Environment], '[is_ansi_nulls_on]',                           [is_ansi_nulls_on]                           )
		,      ( [DBGroupID], [name], [Environment], '[is_ansi_padding_on]',                         [is_ansi_padding_on]                         )
		,      ( [DBGroupID], [name], [Environment], '[is_ansi_warnings_on]',                        [is_ansi_warnings_on]                        )
		,      ( [DBGroupID], [name], [Environment], '[is_arithabort_on]',                           [is_arithabort_on]                           )
		,      ( [DBGroupID], [name], [Environment], '[is_concat_null_yields_null_on]',              [is_concat_null_yields_null_on]              )
		,      ( [DBGroupID], [name], [Environment], '[is_numeric_roundabort_on]',                   [is_numeric_roundabort_on]                   )
		,      ( [DBGroupID], [name], [Environment], '[is_quoted_identifier_on]',                    [is_quoted_identifier_on]                    )
		,      ( [DBGroupID], [name], [Environment], '[is_recursive_triggers_on]',                   [is_recursive_triggers_on]                   )
		,      ( [DBGroupID], [name], [Environment], '[is_cursor_close_on_commit_on]',               [is_cursor_close_on_commit_on]               )
		,      ( [DBGroupID], [name], [Environment], '[is_local_cursor_default]',                    [is_local_cursor_default]                    )
		,      ( [DBGroupID], [name], [Environment], '[is_fulltext_enabled]',                        [is_fulltext_enabled]                        )
		,      ( [DBGroupID], [name], [Environment], '[is_trustworthy_on]',                          [is_trustworthy_on]                          )
		,      ( [DBGroupID], [name], [Environment], '[is_db_chaining_on]',                          [is_db_chaining_on]                          )
		,      ( [DBGroupID], [name], [Environment], '[is_parameterization_forced]',                 [is_parameterization_forced]                 )
		,      ( [DBGroupID], [name], [Environment], '[is_master_key_encrypted_by_server]',          [is_master_key_encrypted_by_server]          )
		,      ( [DBGroupID], [name], [Environment], '[is_query_store_on]',                          [is_query_store_on]                          )
		,      ( [DBGroupID], [name], [Environment], '[is_published]',                               [is_published]                               )
		,      ( [DBGroupID], [name], [Environment], '[is_subscribed]',                              [is_subscribed]                              )
		,      ( [DBGroupID], [name], [Environment], '[is_merge_published]',                         [is_merge_published]                         )
		,      ( [DBGroupID], [name], [Environment], '[is_distributor]',                             [is_distributor]                             )
		,      ( [DBGroupID], [name], [Environment], '[is_sync_with_backup]',                        [is_sync_with_backup]                        )
		,      ( [DBGroupID], [name], [Environment], '[is_broker_enabled]',                          [is_broker_enabled]                          )
		,      ( [DBGroupID], [name], [Environment], '[log_reuse_wait_desc]',                        [log_reuse_wait_desc]                        )
		,      ( [DBGroupID], [name], [Environment], '[is_date_correlation_on]',                     [is_date_correlation_on]                     )
		,      ( [DBGroupID], [name], [Environment], '[is_cdc_enabled]',                             [is_cdc_enabled]                             )
		,      ( [DBGroupID], [name], [Environment], '[is_encrypted]',                               [is_encrypted]                               )
		,      ( [DBGroupID], [name], [Environment], '[is_honor_broker_priority_on]',                [is_honor_broker_priority_on]                )
		,      ( [DBGroupID], [name], [Environment], '[default_language_name]',                      [default_language_name]                      )
		,      ( [DBGroupID], [name], [Environment], '[default_fulltext_language_name]',             [default_fulltext_language_name]             )
		,      ( [DBGroupID], [name], [Environment], '[is_nested_triggers_on]',                      [is_nested_triggers_on]                      )
		,      ( [DBGroupID], [name], [Environment], '[is_transform_noise_words_on]',                [is_transform_noise_words_on]                )
		,      ( [DBGroupID], [name], [Environment], '[two_digit_year_cutoff]',                      [two_digit_year_cutoff]                      )
		,      ( [DBGroupID], [name], [Environment], '[containment_desc]',                           [containment_desc]                           )
		,      ( [DBGroupID], [name], [Environment], '[target_recovery_time_in_seconds]',            [target_recovery_time_in_seconds]            )
		,      ( [DBGroupID], [name], [Environment], '[delayed_durability_desc]',                    [delayed_durability_desc]                    )
		,      ( [DBGroupID], [name], [Environment], '[is_memory_optimized_elevate_to_snapshot_on]', [is_memory_optimized_elevate_to_snapshot_on] )
		,      ( [DBGroupID], [name], [Environment], '[is_federation_member]',                       [is_federation_member]                       )
		,      ( [DBGroupID], [name], [Environment], '[is_remote_data_archive_enabled]',             [is_remote_data_archive_enabled]             )
		,      ( [DBGroupID], [name], [Environment], '[is_mixed_page_allocation_on]',                [is_mixed_page_allocation_on]                )
		)                                   t(id, name, Environment, OptionName, Optionvalue)
)
Select t1.DBGroupID, t1.DBName, t1.OptionName, t1.Optionvalue Production, t2.Optionvalue Development
from t t1
join t t2
on t1.DBGroupID = t2.DBGroupID and t1.DBName = t2.DBName and t1.OptionName = t2.OptionName 
where  t1.Environment = 'Production' and t2.Environment = 'Development' and t1.Optionvalue <> t2.Optionvalue
order by t1.DBName, t1.OptionName

...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
24.08.2020, 11:16
    #39991925
Гавриленко Сергей Алексеевич
Гавриленко Сергей Алексеевич
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите написать запрос
Модератор: Господа! Не умеете писать кратко, пользуйтесь тегом spoiler.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
24.08.2020, 15:46
    #39992006
uaggster
uaggster
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите написать запрос
Кстати: https://sqljana.wordpress.com/2018/09/12/sql-server-compare-the-configuration-of-two-instances-using-sys-configurations/
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
25.08.2020, 06:44
    #39992131
Roust_m
Roust_m
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите написать запрос
uaggster
Кстати: https://sqljana.wordpress.com/2018/09/12/sql-server-compare-the-configuration-of-two-instances-using-sys-configurations/


Это на уровне сервера, мне надо опции баз сравнить. Некоторые опции теряются при бекапе и восстановлении, например: is_broker_enabled
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
25.08.2020, 08:47
    #39992146
uaggster
uaggster
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите написать запрос
Там же и на уровне баз. Дочитайте до конца.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Помогите написать запрос / 17 сообщений из 17, страница 1 из 1
Целевая тема:
Создать новую тему:
Автор:
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
созд. / загр.: 165ms
Закрыть
start [/forum/topic.php?fid=46&mobile=1&tid=1685735]:
 0ms
get settings:
 10ms
get forum list:
 13ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 57ms
get topic data:
 12ms
get forum data:
 2ms
get page messages:
 47ms
get tp. blocked users:
 1ms
others: 15ms
total:  165ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]