Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.databases

  No additional text.


Syntax
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

 
Last revision 2008RTM
See also

  change_tracking_databases (View)
dm_db_mirroring_auto_page_repair (View)
dm_db_script_level (View)
master_files (View)
SCHEMATA (View)
sp_ActiveDirectory_Obj (Procedure)
sp_ActiveDirectory_SCP (Procedure)
sp_adddistributiondb (Procedure)
sp_addmergepublication (Procedure)
sp_addsynctriggers (Procedure)
sp_add_data_file_recover_suspect_db (Procedure)
sp_add_file_recover_suspect_db (Procedure)
sp_add_log_file_recover_suspect_db (Procedure)
sp_add_log_shipping_primary_database (Procedure)
sp_add_log_shipping_primary_secondary (Procedure)
sp_add_log_shipping_secondary_database (Procedure)
sp_add_log_shipping_secondary_primary (Procedure)
sp_add_maintenance_plan_db (Procedure)
sp_attachsubscription (Procedure)
sp_bcp_dbcmptlevel (Procedure)
sp_catalogs_rowset (Procedure)
sp_catalogs_rowset2 (Procedure)
sp_cdc_dbsnapshotLSN (Procedure)
sp_cdc_disable_db_internal (Procedure)
sp_cdc_restoredb_internal (Procedure)
sp_cdc_scan (Procedure)
sp_cdc_vupgrade_databases (Procedure)
sp_certify_removable (Procedure)
sp_changedistpublisher (Procedure)
sp_changedistributiondb (Procedure)
sp_changemergearticle (Procedure)
sp_changereplicationserverpasswords (Procedure)
sp_change_log_shipping_primary_database (Procedure)
sp_change_log_shipping_secondary_database (Procedure)
sp_check_for_owned_jobsteps (Procedure)
sp_check_removable_sysusers (Procedure)
sp_cleanup_log_shipping_history (Procedure)
sp_clear_dbmaintplan_by_db (Procedure)
sp_copysubscription (Procedure)
sp_createstats (Procedure)
sp_create_removable (Procedure)
sp_databases (Procedure)
sp_dbcmptlevel (Procedure)
sp_dbmmonitorchangealert (Procedure)
sp_dbmmonitordropalert (Procedure)
sp_dbmmonitorhelpalert (Procedure)
sp_dbmmonitorMSgetthelatestlsn (Procedure)
sp_dbmmonitorresults (Procedure)
sp_dbmmonitorupdate (Procedure)
sp_dboption (Procedure)
sp_dbremove (Procedure)
sp_db_vardecimal_storage_format (Procedure)
sp_delete_log_shipping_primary_database (Procedure)
sp_delete_log_shipping_primary_secondary (Procedure)
sp_delete_log_shipping_secondary_database_internal (Procedure)
sp_delete_log_shipping_secondary_primary (Procedure)
sp_delete_maintenance_plan (Procedure)
sp_delete_maintenance_plan_db (Procedure)
sp_detach_db (Procedure)
sp_dropdistpublisher (Procedure)
sp_dropdistributiondb (Procedure)
sp_dropdistributor (Procedure)
sp_dropmergepullsubscription (Procedure)
sp_dropmergesubscription (Procedure)
sp_expired_subscription_cleanup (Procedure)
sp_fulltext_database (Procedure)
sp_get_distributor (Procedure)
sp_get_Oracle_publisher_metadata (Procedure)
sp_helparticledts (Procedure)
sp_helpdb (Procedure)
sp_helpdistpublisher (Procedure)
sp_helpdistributiondb (Procedure)
sp_helplogins (Procedure)
sp_helpreplicationdb (Procedure)
sp_helpreplicationdboption (Procedure)
sp_help_log_shipping_monitor (Procedure)
sp_help_log_shipping_primary_database (Procedure)
sp_help_log_shipping_primary_secondary (Procedure)
sp_help_log_shipping_secondary_database (Procedure)
sp_help_maintenance_plan (Procedure)
sp_IHadd_sync_command (Procedure)
sp_instdist (Procedure)
sp_link_publication (Procedure)
sp_logshippinginstallmetadata (Procedure)
sp_makewebtask (Procedure)
sp_mergearticlecolumn (Procedure)
sp_mergesubscription_cleanup (Procedure)
sp_MSadd_distribution_agent (Procedure)
sp_MSadd_logreader_agent (Procedure)
sp_MSadd_log_shipping_error_detail (Procedure)
sp_MSadd_log_shipping_history_detail (Procedure)
sp_MSadd_publication (Procedure)
sp_MSadd_repl_alert (Procedure)
sp_MSadd_repl_command (Procedure)
sp_MSadd_repl_commands27hp (Procedure)
sp_MSadd_subscription (Procedure)
sp_MSarticlecleanup (Procedure)
sp_MSarticle_validation (Procedure)
sp_MScdc_capture_job (Procedure)
sp_MScdc_db_ddl_event (Procedure)
sp_MScdc_ddl_server_trigger (Procedure)
sp_MScdc_job_security_check (Procedure)
sp_MScdc_logddl (Procedure)
sp_MScdc_tranrepl_check (Procedure)
sp_MSCheckmergereplication (Procedure)
sp_MScheckvalidsystables (Procedure)
sp_MScheck_subscription_count_internal (Procedure)
sp_MScleanupmergepublisher_internal (Procedure)
sp_MScleanup_publication_ADinfo (Procedure)
sp_MScreate_dist_tables (Procedure)
sp_MSdbuseraccess (Procedure)
sp_MSdistpublisher_cleanup (Procedure)
sp_MSdodatabasesnapshotinitiation (Procedure)
sp_MSdopartialdatabasesnapshotinitiation (Procedure)
sp_MSdrop_6x_replication_agent (Procedure)
sp_MSdrop_article (Procedure)
sp_MSdrop_cdc (Procedure)
sp_MSdrop_publication (Procedure)
sp_MSdrop_pub_tables (Procedure)
sp_MSdrop_repltran (Procedure)
sp_MSenumallpublications (Procedure)
sp_MSenumallsubscriptions (Procedure)
sp_MSenumthirdpartypublicationvendornames (Procedure)
sp_MSenum_replication_agents (Procedure)
sp_MSenum_replqueues (Procedure)
sp_MSenum_replsqlqueues (Procedure)
sp_MSforeachdb (Procedure)
sp_MSget_last_transaction (Procedure)
sp_MSget_new_xact_seqno (Procedure)
sp_MShasdbaccess (Procedure)
sp_MShelpobjectpublications (Procedure)
sp_MShelp_replication_status (Procedure)
sp_MShelp_replication_table (Procedure)
sp_MSload_tmp_replication_status (Procedure)
sp_MSmergepublishdb (Procedure)
sp_MSpublication_validation (Procedure)
sp_MSpublishdb (Procedure)
sp_MSremovedbreplication (Procedure)
sp_MSremovedbreplication_internal (Procedure)
sp_MSreplcheck_pull (Procedure)
sp_MSreplicationcompatlevel (Procedure)
sp_MSrepltrigcertgrant (Procedure)
sp_MSrepltrigpreparecert (Procedure)
sp_MSrepltrigredefine (Procedure)
sp_MSrepl_addpublication (Procedure)
sp_MSrepl_addpublication_snapshot (Procedure)
sp_MSrepl_articlecolumn (Procedure)
sp_MSrepl_backup_complete (Procedure)
sp_MSrepl_backup_start (Procedure)
sp_MSrepl_clean_replication_bit (Procedure)
sp_MSrepl_DistributorPALAccess (Procedure)
sp_MSrepl_DistributorReplMonitorAccess (Procedure)
sp_MSrepl_dropsubscriber (Procedure)
sp_MSrepl_drop_expired_sub_cleanup_job (Procedure)
sp_MSrepl_enumpublications (Procedure)
sp_MSrepl_enumsubscriptions (Procedure)
sp_MSrepl_helparticle (Procedure)
sp_MSrepl_helppublication (Procedure)
sp_MSrepl_refresh_heterogeneous_publisher (Procedure)
sp_MSrepl_reinitsubscription (Procedure)
sp_MSreset_transaction (Procedure)
sp_MSrestoredbreplication (Procedure)
sp_MSretrieve_publication_attributes (Procedure)
sp_MSscriptdatabase (Procedure)
sp_MSscriptpeerconflictdetection_topology_sendresponse (Procedure)
sp_MSsetupnosyncsubwithlsnatdist (Procedure)
sp_MSset_snapshot_xact_seqno (Procedure)
sp_MSupdate_subscription (Procedure)
sp_MSUpgradeConflictTable (Procedure)
sp_MSverboselogging (Procedure)
sp_MS_marksystemobject (Procedure)
sp_oledb_database (Procedure)
sp_oledb_ro_usrname (Procedure)
sp_refreshreplsysservers (Procedure)
sp_refreshsqlmodule_internal (Procedure)
sp_refresh_log_shipping_monitor (Procedure)
sp_removesrvreplication (Procedure)
sp_rename (Procedure)
sp_renamedb (Procedure)
sp_replicationdboption (Procedure)
sp_replmonitorhelppublisher (Procedure)
sp_replrestart (Procedure)
sp_resetstatus (Procedure)
sp_restoredbreplication (Procedure)
sp_settriggerorder (Procedure)
sp_sqlagent_get_startup_info (Procedure)
sp_sysdac_add_instance (Procedure)
sp_sysdac_drop_database (Procedure)
sp_sysdac_resolve_pending_entry (Procedure)
sp_syspolicy_add_policy_category_subscription (Procedure)
sp_syspolicy_purge_history (Procedure)
sp_syspolicy_update_policy_category_subscription (Procedure)
sp_sysutility_mi_collect_dac_execution_statistics_internal (Procedure)
sp_sysutility_ucp_calculate_dac_file_space_health (Procedure)
sp_sysutility_ucp_calculate_mi_file_space_health (Procedure)
sp_sysutility_ucp_initialize (Procedure)
sp_sysutility_ucp_initialize_mdw (Procedure)
sp_sysutility_ucp_provision_proxy_account (Procedure)
sp_sysutility_ucp_remove (Procedure)
sp_tables (Procedure)
sp_tableswc (Procedure)
sp_unsubscribe (Procedure)
sp_updatestats (Procedure)
sp_upgrade_log_shipping (Procedure)
sp_vupgrade_express_edition (Procedure)
sp_vupgrade_heterogeneous_publishers (Procedure)
sp_vupgrade_mergeobjects (Procedure)
sp_vupgrade_mergetables (Procedure)
sp_vupgrade_publisher (Procedure)
sp_vupgrade_publisherdb (Procedure)
sp_vupgrade_registry (Procedure)
sp_vupgrade_replication (Procedure)
sp_vupgrade_replsecurity_metadata (Procedure)
sp_vupgrade_subpass (Procedure)
sp_vupgrade_subscription_databases (Procedure)
sysdac_instances (View)
sysdatabases (View)
sysutility_ucp_databases (View)
       



News

  Query a named instance
Sybase+ASE+silent+install
SQL 2008 R2
Deprecated procedures in SQL2008
Reporting Services item-level role definitions
Create all your missing indexes
Converting datetime field
Start MSSQL Server Profiler at time
Replicating MSSQL Server views
Exploring Microsoft Sharepoint
The OLE DB provider "SQLNCLI10" for linked server indicates.
Mobile solar charger
Oracle to SQL Server replication
Cannot insert the value NULL into column
Undocumented Microsoft SQL Server 2008
VMware
Zoekmachine optimalisatie
SQL Servers hidden objects
FckEditor
Flash in Ajax
De hype die AJAX heet
Barcode scannen
MySQL; Gratis, makkelijk en snel!
PHP
sIFR; de combinatie tussen HTML en Flash