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>