How to List Databases in SQL Server

Galaxy Glossary

How do I list all databases in SQL Server?

The list databases command shows every database stored on the current SQL Server instance.

Sign up for the latest in SQL knowledge from the Galaxy Team!

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

What is the fastest way to list all databases?

Run SELECT name FROM sys.databases;. It queries the system catalog and returns every database name immediately.

How do I include creation date, owner, or size?

Select more columns from sys.databases, such as create_date, owner_sid, and size. The system view exposes dozens of metadata attributes.

Can I filter the list by keyword?

Add a WHERE clause. For example, WHERE name LIKE '%Sales%' only shows databases relevant to your ecommerce data warehouse.

How do I order databases by creation date?

Attach ORDER BY create_date DESC to surface the newest databases first.

Why use `sys.databases` over `sp_helpdb`?

sys.databases offers better performance, a tabular result set, and full support for WHERE and ORDER BY. It is the modern, documented method.

When should I run this in production?

Listing databases is read-only and low-impact. You can safely execute it anytime, even on busy servers, to verify backup targets or housekeeping jobs.

Best practice: grant only `VIEW ANY DATABASE`

Limit visibility by granting VIEW ANY DATABASE instead of sysadmin. Users see database names but cannot access data.

Why How to List Databases in SQL Server is important

How to List Databases in SQL Server Example Usage


-- Show all databases that contain the standard ecommerce tables
SELECT name AS database_name,
       create_date
FROM sys.databases
WHERE name LIKE '%Commerce%'
ORDER BY create_date DESC;

How to List Databases in SQL Server Syntax


-- Basic list
SELECT name
FROM sys.databases;

-- Include more metadata
SELECT name,
       database_id,
       create_date,
       state_desc,
       compatibility_level
FROM sys.databases;

-- Filter to ecommerce databases that store the Orders and Customers schemas
SELECT name
FROM sys.databases
WHERE name IN ('Ecommerce', 'Ecommerce_Archive');

-- Sort by newest first
SELECT name, create_date
FROM sys.databases
ORDER BY create_date DESC;

Common Mistakes

Running the deprecated stored procedure <code>sp_helpdb</code> instead of querying <code>sys.databases</code>. <code>sys.databases</code> is faster, supports filtering, and remains fully supported in future SQL Server versions.
Granting full <code>sysadmin</code> rights so users can run the query. The correct approach is to grant the minimal <code>VIEW ANY DATABASE</code> permission, keeping data secure while still exposing database names.

Frequently Asked Questions (FAQs)

Do I need special permissions to list databases?

Only the VIEW ANY DATABASE permission is required. Sysadmins implicitly have this right, but lower-privileged roles can be granted it explicitly.

Will the query lock or slow down my server?

No. Reading sys.databases is a metadata lookup held entirely in memory. It does not lock user tables or affect performance.

Can I list databases on a remote server?

Yes. Connect to the remote instance first, then run the same SELECT statement. Tools like Galaxy or SQLCMD support remote connections.

Want to learn about other SQL terms?

dbt tests

dbt tests are declarative assertions in dbt that automatically validate data quality during model builds.

dbt utils

dbt utils is a community-maintained dbt package that adds time-saving utility macros for analytics engineers, such as generating surrogate keys, safely unioning models, and automating date spines.

dbt Tutorial Glossary: Build, Test & Deploy SQL Models

dbt is an open-source framework that lets data teams transform raw warehouse tables into tested, documented, production-ready datasets using version-controlled SQL.

Trusted by top engineers on high-velocity teams

Assort Health

Curri

Rubie

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.