SQL Server/T-SQL Tutorial/System Tables Views/sysdatabases

sysdatabases contains a row for every system and user-defined database on the SQL Server system.

It appears only in the master database.
5>
6> select  top 10 * from sysdatabases;
7> GO
name                                                                                                                             dbid   sid

                                                   mode   status      status2     crdate                  reserved                category    cmptlevel filename
             version
-------------------------------------------------------------------------------------------------------------------------------- ------ ----------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------- ------ ----------- ----------- ----------------------- ----------------------- ----------- --------- ------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------ -------
master                                                                                                                                1 0x01

                                                        0       65544  1090520064 2003-04-08 09:13:36.390 1900-01-01 00:00:00.000           0        90 c:\Program Files\Microsoft SQL Server\MSSQL.1\MS
SQL\DATA\master.mdf
                 611
tempdb                                                                                                                                2 0x01

                                                        0           8  1090520064 2008-08-07 20:57:36.090 1900-01-01 00:00:00.000           0        90 c:\Program Files\Microsoft SQL Server\MSSQL.1\MS
SQL\DATA\tempdb.mdf
                 611
model                                                                                                                                 3 0x01

                                                        0       65544  1090519040 2003-04-08 09:13:36.390 1900-01-01 00:00:00.000           0        90 c:\Program Files\Microsoft SQL Server\MSSQL.1\MS
SQL\DATA\model.mdf
                 611
msdb                                                                                                                                  4 0x01

                                                        0       65544  1627390976 2005-10-14 01:54:05.240 1900-01-01 00:00:00.000           0        90 c:\Program Files\Microsoft SQL Server\MSSQL.1\MS
SQL\DATA\MSDBData.mdf
                 611
(4 rows affected)

Verifying the existence of the default database.

14>
15>     CREATE PROC pr_verifydb (@dbname varchar(30))
16>     AS
17>     DECLARE @dbid int
18>     SELECT @dbid = dbid from sysdatabases
19>          where name = @dbname
20>     IF @@rowcount = 0
21>          RETURN 50
22>     RETURN 0 -- Everything is perfect!!!
23>     GO
1>
2>