- title What the fastest way of finding the databases and their status with MSSQL
- )
What the fastest way of finding the databases and their status with MSSQL
There are many ways of finding the various databases and their statuses:
- From the Visual Studio for a quick look at what are the databases on that server, but finding their statuses is more difficult.
- To querying the sys.databases for MSSQL 2005/2008 or querying the the sysdatabases for MSSQL 2000.
- To running sp_helpdb which gives you a quick status of all the databases.
Applies to:
- Microsoft SQL Server 2000
- Microsoft SQL Server 2005
- Microsoft SQL Server 2008
sp_helpdb
- USE master
- EXEC SP_HELPDB
name db_size owner dbid created status compatibility_level -------------------------------------------------------------------------------------------------------------------------------- ------------- -------------------------------------------------------------------------------------------------------------------------------- ------ ----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------- AdventureWorks 181.94 MB SQLSERVER\froggy 8 May 7 2007 Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=FULL, Version=611, Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52, IsAnsiNullsEnabled, IsAnsiPaddingEnabled, IsAnsiWarningsEnabled, IsArithmeticAbortEnabled, IsAutoCreateStat 90 AdventureWorksDW 70.50 MB SQLSERVER\froggy 7 May 7 2007 Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=611, Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52, IsAnsiNullsEnabled, IsAnsiPaddingEnabled, IsAnsiWarningsEnabled, IsArithmeticAbortEnabled, IsAutoCreateSt 90 AdventureWorksLT 7.00 MB SQLSERVER\froggy 9 May 10 2007 Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=611, Collation=Latin1_General_CI_AS, SQLSortOrder=0, IsAnsiNullsEnabled, IsAnsiPaddingEnabled, IsAnsiWarningsEnabled, IsArithmeticAbortEnabled, IsAutoCreateStatistics, 90 master 4.50 MB sa 1 Apr 8 2003 Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=611, Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52, IsAutoCreateStatistics, IsAutoUpdateStatistics 90 model 1.69 MB sa 3 Apr 8 2003 Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=FULL, Version=611, Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52, IsAutoCreateStatistics, IsAutoUpdateStatistics 90 msdb 7.06 MB sa 4 Oct 14 2005 Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=611, Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52, IsAutoCreateStatistics, IsAutoUpdateStatistics, IsFullTextEnabled 90 ReportServer 3.94 MB SQLSERVER\froggy 5 May 7 2007 Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=611, Collation=Latin1_General_CI_AS_KS_WS, SQLSortOrder=0, IsAutoCreateStatistics, IsAutoUpdateStatistics, IsFullTextEnabled 90 ReportServerTempDB 2.94 MB SQLSERVER\froggy 6 May 7 2007 Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=611, Collation=Latin1_General_CI_AS_KS_WS, SQLSortOrder=0, IsAutoCreateStatistics, IsAutoUpdateStatistics, IsFullTextEnabled 90 sql911 20.24 MB SQLSERVER\froggy 10 Oct 22 2007 Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=611, Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52, IsAutoShrink, IsAutoCreateStatistics, IsAutoUpdateStatistics 90 tempdb 11.75 MB sa 2 Feb 13 2008 Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=611, Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52, IsAutoCreateStatistics, IsAutoUpdateStatistics 90
sys.databases
- The output of sys.databases gives very similar information to sp_helpdb, but sp_helpdb is much more compact and easier to read.
- USE master
- SELECT * FROM sys.databases;
- go
name database_id source_database_id owner_sid create_date compatibility_level collation_name user_access user_access_desc is_read_only is_auto_close_on is_auto_shrink_on state state_desc is_in_standby is_cleanly_shutdown is_supplemental_logging_enabled snapshot_isolation_state snapshot_isolation_state_desc is_read_committed_snapshot_on recovery_model recovery_model_desc page_verify_option page_verify_option_desc is_auto_create_stats_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_published is_subscribed is_merge_published is_distributor is_sync_with_backup service_broker_guid is_broker_enabled log_reuse_wait log_reuse_wait_desc is_date_correlation_on -------------------------------------------------------------------------------------------------------------------------------- ----------- ------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------- ------------------- -------------------------------------------------------------------------------------------------------------------------------- ----------- ------------------------------------------------------------ ------------ ---------------- ----------------- ----- ------------------------------------------------------------ ------------- ------------------- ------------------------------- ------------------------ ------------------------------------------------------------ ----------------------------- -------------- ------------------------------------------------------------ ------------------ ------------------------------------------------------------ ----------------------- ----------------------- ----------------------------- ----------------------- ---------------- ------------------ ------------------- ---------------- ----------------------------- ------------------------ ----------------------- ------------------------ ---------------------------- ----------------------- ------------------- ----------------- ----------------- -------------------------- --------------------------------- ------------ ------------- ------------------ -------------- ------------------- ------------------------------------ ----------------- -------------- ------------------------------------------------------------ ---------------------- master 1 NULL 0x01 2003-04-08 09:13:36.390 90 SQL_Latin1_General_CP1_CI_AS 0 MULTI_USER 0 0 0 0 ONLINE 0 0 0 1 ON 0 3 SIMPLE 2 CHECKSUM 1 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 00000000-0000-0000-0000-000000000000 0 4 ACTIVE_TRANSACTION 0 tempdb 2 NULL 0x01 2008-02-13 12:36:51.060 90 SQL_Latin1_General_CP1_CI_AS 0 MULTI_USER 0 0 0 0 ONLINE 0 0 0 0 OFF 0 3 SIMPLE 0 NONE 1 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 7A54F8A9-A805-44A9-BF9C-591782F38454 1 0 NOTHING 0 model 3 NULL 0x01 2003-04-08 09:13:36.390 90 SQL_Latin1_General_CP1_CI_AS 0 MULTI_USER 0 0 0 0 ONLINE 0 0 0 0 OFF 0 1 FULL 2 CHECKSUM 1 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 00000000-0000-0000-0000-000000000000 0 0 NOTHING 0 msdb 4 NULL 0x01 2005-10-14 01:54:05.240 90 SQL_Latin1_General_CP1_CI_AS 0 MULTI_USER 0 0 0 0 ONLINE 0 0 0 1 ON 0 3 SIMPLE 2 CHECKSUM 1 1 0 0 0 0 0 0 0 0 0 0 0 0 1 1 1 0 0 0 0 0 0 0 F89183BD-74EC-41B7-A865-DA887979861D 1 0 NOTHING 0 ReportServer 5 NULL 0x01050000000000051500000059843EB8EA39F604BD3BFDE9EB030000 2007-05-07 10:40:08.403 90 Latin1_General_CI_AS_KS_WS 0 MULTI_USER 0 0 0 0 ONLINE 0 0 0 0 OFF 0 3 SIMPLE 2 CHECKSUM 1 1 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 B6C6A521-ABEF-4EEC-AF13-99EACAC41131 1 0 NOTHING 0 ReportServerTempDB 6 NULL 0x01050000000000051500000059843EB8EA39F604BD3BFDE9EB030000 2007-05-07 10:40:10.500 90 Latin1_General_CI_AS_KS_WS 0 MULTI_USER 0 0 0 0 ONLINE 0 0 0 0 OFF 0 3 SIMPLE 2 CHECKSUM 1 1 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 AB88A291-F774-4974-976A-2518276FDEDB 1 0 NOTHING 0 AdventureWorksDW 7 NULL 0x01050000000000051500000059843EB8EA39F604BD3BFDE9EB030000 2007-05-07 11:03:48.607 90 SQL_Latin1_General_CP1_CI_AS 0 MULTI_USER 0 0 0 0 ONLINE 0 0 0 1 ON 0 3 SIMPLE 2 CHECKSUM 1 1 0 0 1 1 1 1 1 0 1 0 0 0 1 0 0 0 0 0 0 0 0 0 FF774B6C-520A-4B79-B4E9-ADE9BC7756E4 1 0 NOTHING 0 AdventureWorks 8 NULL 0x01050000000000051500000059843EB8EA39F604BD3BFDE9EB030000 2007-05-07 11:03:51.250 90 SQL_Latin1_General_CP1_CI_AS 0 MULTI_USER 0 0 0 0 ONLINE 0 0 0 0 OFF 0 1 FULL 2 CHECKSUM 1 1 0 0 1 1 1 1 1 0 1 0 0 0 1 0 0 0 0 0 0 0 0 0 ECCA7098-AF58-49EC-B510-DB8280F73799 1 2 LOG_BACKUP 0 AdventureWorksLT 9 NULL 0x01050000000000051500000059843EB8EA39F604BD3BFDE9EB030000 2007-05-10 09:55:36.327 90 Latin1_General_CI_AS 0 MULTI_USER 0 0 0 0 ONLINE 0 0 0 0 OFF 0 3 SIMPLE 2 CHECKSUM 1 1 0 0 1 1 1 1 1 0 1 0 0 0 1 0 0 0 0 0 0 0 0 0 5D1B9310-F8AA-4E61-8A68-7C0C32143F26 0 0 NOTHING 0 sql911 10 NULL 0x01050000000000051500000059843EB8EA39F604BD3BFDE9EB030000 2007-10-22 09:30:51.020 90 SQL_Latin1_General_CP1_CI_AS 0 MULTI_USER 0 0 1 0 ONLINE 0 0 0 0 OFF 0 3 SIMPLE 2 CHECKSUM 1 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 147D09F5-220C-4A22-94B3-937F2AC73317 0 0 NOTHING 0 (10 row(s) affected)
- The output of sp_helpdb is much easier to read than the output of sys.databases.
- Here is the stored procedure sp_helpdb, see: What are the definitions of the MS stored procedures?
- USE master
- EXEC SP_HELPTEXT SP_HELPDB
- go
Text
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
create procedure sys.sp_helpdb -- 1995/12/20 15:34 #12755
@dbname sysname = NULL -- database name
as
declare @exec_stmt nvarchar(625)
declare @showdev bit
declare @name sysname
declare @cmd nvarchar(279)
declare @low nvarchar(11)
declare @dbdesc varchar(600) /* the total description for the db */
declare @propdesc varchar(40)
set nocount on
/* Create temp table before any DMP to enure dynamic
** Since we examine the status bits in sysdatabase and turn them
** into english, we need a temporary table to build the descriptions.
*/
create table #spdbdesc
(
dbname sysname,
owner sysname null,
created nvarchar(11),
dbid smallint,
dbdesc nvarchar(600) null,
dbsize nvarchar(13) null,
cmptlevel tinyint
)
/*
** If no database name given, get 'em all.
*/
if @dbname is null
select @showdev = 0
else select @showdev = 1
/*
** See if the database exists
*/
if not exists (select * from master.dbo.sysdatabases
where (@dbname is null or name = @dbname))
begin
raiserror(15010,-1,-1,@dbname)
return (1)
end
select @low = convert(varchar(11),low) from master.dbo.spt_values
where type = N'E' and number = 1
/*
** Initialize #spdbdesc from sysdatabases
*/
insert into #spdbdesc (dbname, owner, created, dbid, cmptlevel)
select name, suser_sname(sid), convert(nvarchar(11), crdate),
dbid, cmptlevel from master.dbo.sysdatabases
where (@dbname is null or name = @dbname)
/*
** Check if you have access to database
** if have access set size and collation
*/
select @low = convert(varchar(11),low) from master.dbo.spt_values
where type = N'E' and number = 1
declare ms_crs_c1 cursor global for
select db_name (dbid) from #spdbdesc
open ms_crs_c1
fetch ms_crs_c1 into @name
while @@fetch_status >= 0
begin
if (has_dbaccess(@name) <> 1)
begin
delete #spdbdesc where current of ms_crs_c1
raiserror(15622,-1,-1, @name)
end
else
begin
/* Insert row for each database */
select @exec_stmt = 'update #spdbdesc
set dbsize = (select str(convert(dec(15),sum(size))* ' + @low + '/ 1048576,10,2)+ N'' MB'' from '
+ quotename(@name, N'[') + N'.dbo.sysfiles) WHERE current of ms_crs_c1'
execute (@exec_stmt)
end
fetch ms_crs_c1 into @name
end
deallocate ms_crs_c1
/*
** Now for each dbid in #spdbdesc, build the database status
** description.
*/
declare @curdbid smallint /* the one we're currently working on */
/*
** Set @curdbid to the first dbid.
*/
select @curdbid = min(dbid) from #spdbdesc
while @curdbid IS NOT NULL
begin
set @name = db_name(@curdbid)
-- These properties always available
SELECT @dbdesc = 'Status=' + convert(sysname,DatabasePropertyEx(@name,'Status'))
SELECT @dbdesc = @dbdesc + ', Updateability=' + convert(sysname,DatabasePropertyEx(@name,'Updateability'))
SELECT @dbdesc = @dbdesc + ', UserAccess=' + convert(sysname,DatabasePropertyEx(@name,'UserAccess'))
SELECT @dbdesc = @dbdesc + ', Recovery=' + convert(sysname,DatabasePropertyEx(@name,'Recovery'))
SELECT @dbdesc = @dbdesc + ', Version=' + convert(sysname,DatabasePropertyEx(@name,'Version'))
-- These props only available if db not shutdown
IF DatabaseProperty(@name, 'IsShutdown') = 0
BEGIN
SELECT @dbdesc = @dbdesc + ', Collation=' + convert(sysname,DatabasePropertyEx(@name,'Collation'))
SELECT @dbdesc = @dbdesc + ', SQLSortOrder=' + convert(sysname,DatabasePropertyEx(@name,'SQLSortOrder'))
END
-- These are the boolean properties
IF DatabasePropertyEx(@name,'IsAutoClose') = 1
SELECT @dbdesc = @dbdesc + ', ' + 'IsAutoClose'
IF DatabasePropertyEx(@name,'IsAutoShrink') = 1
SELECT @dbdesc = @dbdesc + ', ' + 'IsAutoShrink'
IF DatabasePropertyEx(@name,'IsInStandby') = 1
SELECT @dbdesc = @dbdesc + ', ' + 'IsInStandby'
IF DatabasePropertyEx(@name,'IsTornPageDetectionEnabled') = 1
SELECT @dbdesc = @dbdesc + ', ' + 'IsTornPageDetectionEnabled'
IF DatabasePropertyEx(@name,'IsAnsiNullDefault') = 1
SELECT @dbdesc = @dbdesc + ', ' + 'IsAnsiNullDefault'
IF DatabasePropertyEx(@name,'IsAnsiNullsEnabled') = 1
SELECT @dbdesc = @dbdesc + ', ' + 'IsAnsiNullsEnabled'
IF DatabasePropertyEx(@name,'IsAnsiPaddingEnabled') = 1
SELECT @dbdesc = @dbdesc + ', ' + 'IsAnsiPaddingEnabled'
IF DatabasePropertyEx(@name,'IsAnsiWarningsEnabled') = 1
SELECT @dbdesc = @dbdesc + ', ' + 'IsAnsiWarningsEnabled'
IF DatabasePropertyEx(@name,'IsArithmeticAbortEnabled') = 1
SELECT @dbdesc = @dbdesc + ', ' + 'IsArithmeticAbortEnabled'
IF DatabasePropertyEx(@name,'IsAutoCreateStatistics') = 1
SELECT @dbdesc = @dbdesc + ', ' + 'IsAutoCreateStatistics'
IF DatabasePropertyEx(@name,'IsAutoUpdateStatistics') = 1
SELECT @dbdesc = @dbdesc + ', ' + 'IsAutoUpdateStatistics'
IF DatabasePropertyEx(@name,'IsCloseCursorsOnCommitEnabled') = 1
SELECT @dbdesc = @dbdesc + ', ' + 'IsCloseCursorsOnCommitEnabled'
IF DatabasePropertyEx(@name,'IsFullTextEnabled') = 1
SELECT @dbdesc = @dbdesc + ', ' + 'IsFullTextEnabled'
IF DatabasePropertyEx(@name,'IsLocalCursorsDefault') = 1
SELECT @dbdesc = @dbdesc + ', ' + 'IsLocalCursorsDefault'
IF DatabasePropertyEx(@name,'IsNullConcat') = 1
SELECT @dbdesc = @dbdesc + ', ' + 'IsNullConcat'
IF DatabasePropertyEx(@name,'IsNumericRoundAbortEnabled') = 1
SELECT @dbdesc = @dbdesc + ', ' + 'IsNumericRoundAbortEnabled'
IF DatabasePropertyEx(@name,'IsQuotedIdentifiersEnabled') = 1
SELECT @dbdesc = @dbdesc + ', ' + 'IsQuotedIdentifiersEnabled'
IF DatabasePropertyEx(@name,'IsRecursiveTriggersEnabled') = 1
SELECT @dbdesc = @dbdesc + ', ' + 'IsRecursiveTriggersEnabled'
IF DatabasePropertyEx(@name,'IsMergePublished') = 1
SELECT @dbdesc = @dbdesc + ', ' + 'IsMergePublished'
IF DatabasePropertyEx(@name,'IsPublished') = 1
SELECT @dbdesc = @dbdesc + ', ' + 'IsPublished'
IF DatabasePropertyEx(@name,'IsSubscribed') = 1
SELECT @dbdesc = @dbdesc + ', ' + 'IsSubscribed'
IF DatabasePropertyEx(@name,'IsSyncWithBackup') = 1
SELECT @dbdesc = @dbdesc + ', ' + 'IsSyncWithBackup'
update #spdbdesc set dbdesc = @dbdesc where dbid = @curdbid
/*
** Now get the next, if any dbid.
*/
select @curdbid = min(dbid) from #spdbdesc where dbid > @curdbid
end
/*
** Now #spdbdesc is complete so we can print out the db info
*/
select name = dbname,
db_size = dbsize,
owner = owner,
dbid = dbid,
created = created,
status = dbdesc,
compatibility_level = cmptlevel
from #spdbdesc
order by dbname
/*
** If we are looking at one database, show its file allocation.
*/
if @showdev = 1 and has_dbaccess(@dbname) = 1
begin
print N' '
select @cmd = N'use ' + quotename(@dbname) + N' exec sys.sp_helpfile'
exec (@cmd)
end
return (0) -- sp_helpdb