Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

List all the databases on one SQL Server in the order they were created

I have probably in excess of 100 databases on this one SQL Server (2005) instance. I'd like to list them in order of their create dates, or even better, in the order of the date of the latest modification to any table.

Is there a SELECT query I can write, and just as importantly, from what context do I write it and with what permissions do I need to execute it?

like image 479
John Dunagan Avatar asked Sep 18 '13 15:09

John Dunagan


People also ask

What is the query to list all the databases?

The command to see system databases are : SELECT name, database_id, create_date FROM sys.

How do I get a list of databases and sizes in SQL Server?

If you need to check a single database, you can quickly find the SQL Server database sizein SQL Server Management Studio (SSMS): Right-click the database and then click Reports -> Standard Reports -> Disk Usage. Alternatively, you can use stored procedures like exec sp_spaceused to get database size.


1 Answers

You can easily write this query against the sys.databases catalog view

SELECT * FROM sys.databases
ORDER BY create_date 

but unfortunately, there's no equivalent for the "last modification date" that I'm aware of ...

This should work from any database on that server - doesn't matter which database you're in, those sys catalog views should be accessible from anywhere.

like image 194
marc_s Avatar answered Sep 17 '22 14:09

marc_s