Now, I realized I made a few listing queries over the years: how to list all stored procedures, how to list all tables with a given name, how to list all connections... but I just realized I never did the simplest of them all:
How to list all databases on a server.
To do this, we can query the sys.databases system table. This table can provide us with a wealth of database knowledge such as:
- Database Name
- Database ID
- Creation Date
- State
- Collation
You can filter on any of these fields as you would a regular query.
Please see below for a simple example query I've had to use recently:
SELECT
s.name as DatabaseName,
s.database_id as DatabaseID,
s.create_date as CreationDate,
s.collation_name as Collation,
s.recovery_model as RecoveryModel,
s.state_desc
FROM
sys.databases s
WHERE
create_date >= '01/01/2020'
ORDER BY name;
I hope this was useful to someone. :-)
DISCLAIMER: As stated, I’m not an expert so please, PLEASE feel free to politely correct or comment as you see fit. Your feedback is always welcomed. :-)
