Find all the database information from sys.databases
when i connect to SQL Server 2005/2008 first time, I always run this query to find the databases information.
SELECT name ,database_id,create_date,compatibility_level,collation_name,user_access,is_read_only,is_auto_close_on,is_auto_shrink_on,state,state_desc,recovery_model,recovery_model_desc,page_verify_option,page_verify_option_desc,is_auto_create_stats_on,is_auto_update_stats_on
FROM sys.databases
SELECT name ,database_id,create_date,compatibility_level,collation_name,user_access,is_read_only,is_auto_close_on,is_auto_shrink_on,state,state_desc,recovery_model,recovery_model_desc,page_verify_option,page_verify_option_desc,is_auto_create_stats_on,is_auto_update_stats_on
FROM sys.databases
Auto create stats turned on sql database
If you have a query execution issues first thing you should look at your database to check auto_create and auto_update stats is turned on or off?
select name, is_auto_create_stats_on ,is_auto_update_stats_on
from sys.databases
where name = 'AdventureWorks2008' -- your db name here
0 - turned off
1 - turned on
The following query will list out all dbs
SELECT name AS "DbName",
is_auto_create_stats_on AS "Auto Create Stats",
is_auto_update_stats_on AS "Auto Update Stats",
is_read_only AS "Read Only"
FROM sys.databases
WHERE database_ID > 4;
GO
select name, is_auto_create_stats_on ,is_auto_update_stats_on
from sys.databases
where name = 'AdventureWorks2008' -- your db name here
0 - turned off
1 - turned on
The following query will list out all dbs
SELECT name AS "DbName",
is_auto_create_stats_on AS "Auto Create Stats",
is_auto_update_stats_on AS "Auto Update Stats",
is_read_only AS "Read Only"
FROM sys.databases
WHERE database_ID > 4;
GO