sys.databases
sys.databases (Transact-SQL) - SQL Server | Microsoft Learn
データベースの状態の確認
code:sql
select
d.name
, case d.is_read_only
when 0 then 'READ_ONLY'
when 1 then 'READ_WRITE'
else convert(varchar (1), d.is_read_only)
end as read_only
, d.state_desc
, case d.is_in_standby
when 0 then '復元ログに対し、読み取り専用'
else convert(varchar (1), d.is_in_standby)
end as is_in_standby
, case d.is_cleanly_shutdown
when 0 then '起動時、復旧必要なし'
when 1 then '起動時、復旧必要あり'
else convert(varchar (1), d.is_cleanly_shutdown)
end as is_cleanly_shutdown
, case d.is_supplemental_logging_enabled
when 0 then 'OFF'
when 1 then 'ON'
else convert(varchar (1), d.is_supplemental_logging_enabled)
end as is_supplemental_logging_enabled
, d.snapshot_isolation_state_desc
, case d.is_read_committed_snapshot_on
when 0 then 'OFF'
when 1 then 'ON'
else convert(varchar (1), d.is_read_committed_snapshot_on)
end as is_read_committed_snapshot_on
, d.recovery_model_desc
, d.page_verify_option_desc
, case d.is_ansi_null_default_on
when 0 then 'OFF'
when 1 then 'ON'
else convert(varchar (1), d.is_ansi_null_default_on)
end as is_ansi_null_default_on
, case d.is_ansi_nulls_on
when 0 then 'OFF'
when 1 then 'ON'
else convert(varchar (1), d.is_ansi_nulls_on)
end as is_ansi_nulls_on
, case d.is_ansi_padding_on
when 0 then 'OFF'
when 1 then 'ON'
else convert(varchar (1), d.is_ansi_padding_on)
end as is_ansi_padding_on
, case d.is_ansi_warnings_on
when 0 then 'OFF'
when 1 then 'ON'
else convert(varchar (1), d.is_ansi_warnings_on)
end as is_ansi_warnings_on
, case d.is_arithabort_on
when 0 then 'OFF'
when 1 then 'ON'
else convert(varchar (1), d.is_arithabort_on)
end as is_arithabort_on
, case d.is_concat_null_yields_null_on
when 0 then 'OFF'
when 1 then 'ON'
else convert(varchar (1), d.is_concat_null_yields_null_on)
end as is_concat_null_yields_null_on
, case d.is_numeric_roundabort_on
when 0 then 'OFF'
when 1 then 'ON'
else convert(varchar (1), d.is_numeric_roundabort_on)
end as is_numeric_roundabort_on
, case d.is_quoted_identifier_on
when 0 then 'OFF'
when 1 then 'ON'
else convert(varchar (1), d.is_quoted_identifier_on)
end as is_quoted_identifier_on
, case d.is_recursive_triggers_on
when 0 then 'OFF'
when 1 then 'ON'
else convert(varchar (1), d.is_recursive_triggers_on)
end as is_recursive_triggers_on
, case d.is_cursor_close_on_commit_on
when 0 then 'OFF'
when 1 then 'ON'
else convert(varchar (1), d.is_cursor_close_on_commit_on)
end as is_cursor_close_on_commit_on
, case d.is_local_cursor_default
when 0 then 'GLOBAL'
when 1 then 'LOCAL'
else convert(varchar (1), d.is_local_cursor_default)
end as is_local_cursor_default
, case d.is_fulltext_enabled
when 0 then '無効'
when 1 then '有効'
else convert(varchar (1), d.is_fulltext_enabled)
end as is_fulltext_enabled
, case d.is_query_store_on
when 0 then '無効'
when 1 then '有効'
else convert(varchar (1), d.is_query_store_on)
end as is_query_store_on
, case d.log_reuse_wait
when 0 then ''
else convert(varchar (1), d.log_reuse_wait)
end as log_reuse_wait
, d.log_reuse_wait_desc
from
sys.databases as d
;
table:result
name state_desc ...
master ONLINE
tempdb ONLINE
model ONLINE
msdb ONLINE
sample ONLINE
sample2 RESTORING
state
0 = ONLINE
1 = RESTORING
2 = RECOVERING
3 = RECOVERY_PENDING
4 = SUSPECT
5 = EMERGENCY
6 = OFFLINE
7 = COPYING
10 = OFFLINE_SECONDARY