CREATE VIEW sys.databases AS
SELECT d.name, d.id AS database_id,
r.indepid AS source_database_id,
d.sid AS owner_sid,
d.crdate AS create_date,
d.cmptlevel AS compatibility_level,
convert(sysname, CollationPropertyFromID(p.cid, 'name')) AS collation_name,
p.user_access, ua.name AS user_access_desc,
sysconv(bit, d.status & 0x400) AS is_read_only, -- DBR_RDONLY
sysconv(bit, d.status & 1) AS is_auto_close_on, -- DBR_CLOSE_ON_EXIT
sysconv(bit, d.status & 0x400000) AS is_auto_shrink_on, -- DBR_AUTOSHRINK
p.state, st.name AS state_desc,
sysconv(bit, d.status & 0x200000) AS is_in_standby, -- DBR_STANDBY
sysconv(bit, d.status & 0x40000000) AS is_cleanly_shutdown, -- DBR_CLEANLY_SHUTDOWN
sysconv(bit, d.status & 0x80000000) AS is_supplemental_logging_enabled, -- DBR_SUPPLEMENT_LOG
p.snapshot_isolation_state, si.name AS snapshot_isolation_state_desc,
sysconv(bit, d.status & 0x800000) AS is_read_committed_snapshot_on, -- DBR_READCOMMITTED_SNAPSHOT
p.recovery_model, ro.name AS recovery_model_desc,
p.page_verify_option, pv.name AS page_verify_option_desc,
sysconv(bit, d.status2 & 0x1000000) AS is_auto_create_stats_on, -- DBR_AUTOCRTSTATS
sysconv(bit, d.status2 & 0x40000000) AS is_auto_update_stats_on, -- DBR_AUTOUPDSTATS
sysconv(bit, d.status2 & 0x80000000) AS is_auto_update_stats_async_on, -- DBR_AUTOUPDSTATSASYNC
sysconv(bit, d.status2 & 0x4000) AS is_ansi_null_default_on, -- DBR_ANSINULLDFLT
sysconv(bit, d.status2 & 0x4000000) AS is_ansi_nulls_on, -- DBR_ANSINULLS
sysconv(bit, d.status2 & 0x2000) AS is_ansi_padding_on, -- DBR_ANSIPADDING
sysconv(bit, d.status2 & 0x10000000) AS is_ansi_warnings_on, -- DBR_ANSIWARNINGS
sysconv(bit, d.status2 & 0x1000) AS is_arithabort_on, -- DBR_ARITHABORT
sysconv(bit, d.status2 & 0x10000) AS is_concat_null_yields_null_on, -- DBR_CATNULL
sysconv(bit, d.status2 & 0x800) AS is_numeric_roundabort_on, -- DBR_NUMEABORT
sysconv(bit, d.status2 & 0x800000) AS is_quoted_identifier_on, -- DBR_QUOTEDIDENT
sysconv(bit, d.status2 & 0x20000) AS is_recursive_triggers_on, -- DBR_RECURTRIG
sysconv(bit, d.status2 & 0x2000000) AS is_cursor_close_on_commit_on, -- DBR_CURSCLOSEONCOM
sysconv(bit, d.status2 & 0x100000) AS is_local_cursor_default, -- DBR_DEFLOCALCURS
sysconv(bit, d.status2 & 0x20000000) AS is_fulltext_enabled, -- DBR_FTENABLED
sysconv(bit, d.status2 & 0x200) AS is_trustworthy_on, -- DBR_TRUSTWORTHY
sysconv(bit, d.status2 & 0x400) AS is_db_chaining_on, -- DBR_DBCHAINING
sysconv(bit, d.status2 & 0x08000000) AS is_parameterization_forced, -- DBR_UNIVERSALAUTOPARAM
sysconv(bit, d.status2 & 64) AS is_master_key_encrypted_by_server, -- DBR_MASTKEY
sysconv(bit, d.category & 1) AS is_published,
sysconv(bit, d.category & 2) AS is_subscribed,
sysconv(bit, d.category & 4) AS is_merge_published,
sysconv(bit, d.category & 16) AS is_distributor,
sysconv(bit, d.category & 32) AS is_sync_with_backup,
d.svcbrkrguid AS service_broker_guid,
sysconv(bit, case when d.scope = 0 then 1 else 0 end) AS is_broker_enabled,
p.log_reuse_wait, lr.name AS log_reuse_wait_desc,
sysconv(bit, d.status2 & 4) AS is_date_correlation_on, -- DBR_DATECORRELATIONOPT
sysconv(bit, d.category & 64) AS is_cdc_enabled,
sysconv(bit, d.status2 & 0x100) AS is_encrypted, -- DBR_ENCRYPTION
sysconv(bit, d.status2 & 0x8) AS is_honor_broker_priority_on -- DBR_HONORBRKPRI
FROM master.sys.sysdbreg d OUTER APPLY OpenRowset(TABLE DBPROP, d.id) p
LEFT JOIN sys.syssingleobjrefs r ON r.depid = d.id AND r.class = 96 AND r.depsubid = 0 -- SRC_VIEWPOINTDB
LEFT JOIN sys.syspalvalues st ON st.class = 'DBST' AND st.value = p.state
LEFT JOIN sys.syspalvalues ua ON ua.class = 'DBUA' AND ua.value = p.user_access
LEFT JOIN sys.syspalvalues si ON si.class = 'DBSI' AND si.value = p.snapshot_isolation_state
LEFT JOIN sys.syspalvalues ro ON ro.class = 'DBRO' AND ro.value = p.recovery_model
LEFT JOIN sys.syspalvalues pv ON pv.class = 'DBPV' AND pv.value = p.page_verify_option
LEFT JOIN sys.syspalvalues lr ON lr.class = 'LRWT' AND lr.value = p.log_reuse_wait
WHERE d.id < 0x7fff
AND has_access('DB', d.id) = 1
|
