What are all the databases on MSSQL

Applies to:

  • MS SQL Server 7
  • MS SQL Server 2000
  • MS SQL Server 2005

Database listings

USE master;
go

SELECT SUBSTRING(name,1,30) AS 'Database',
       crdate AS 'Created',
       SUBSTRING(filename,1,95) AS 'Disk file'
FROM sysdatabases
ORDER BY name;
go
Database                       Created                 Disk file
------------------------------ ----------------------- -----------------------------------------------------------------------------------------------
AdventureWorks                 2007-05-07 11:03:51.250 C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_Data.mdf
AdventureWorksDW               2007-05-07 11:03:48.607 C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorksDW_Data.mdf
AdventureWorksLT               2007-05-10 09:55:36.327 C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorksLT_Data.mdf
master                         2003-04-08 09:13:36.390 C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf
model                          2003-04-08 09:13:36.390 C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\model.mdf
msdb                           2005-10-14 01:54:05.240 C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\MSDBData.mdf
ReportServer                   2007-05-07 10:40:08.403 C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\ReportServer.mdf
ReportServerTempDB             2007-05-07 10:40:10.500 C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\ReportServerTempDB.mdf
sql911                         2007-05-10 10:06:45.513 C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\sql911.mdf
tempdb                         2007-08-07 08:45:05.763 c:\syv\tempdb.mdf

(10 row(s) affected)
  • Microsoft keeps track of all the databases in the MASTER database.
  • All the information needed is the SYSDATABASES table.