20

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?

gotqn
  • 42,737
  • 46
  • 157
  • 243
John Dunagan
  • 1,445
  • 3
  • 18
  • 30

3 Answers3

36

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • 2
    Modify_date is the column next to Create_date in sys.tables, probably just query that to see when the last table on the DB was modified – orgtigger Sep 18 '13 at 16:06
  • 1
    @orgtigger: the `modified_date` is the last time the table was **structurally** modified (e.g. column added, dropped etc.) - but not the last time some of its data was changed – marc_s Sep 18 '13 at 19:49
  • And sys.databases, in SQL 2005, didn't have that column. marc_s is also correct that I'm looking for last data change in the db, as that would best approximate for me the recency of interaction (even if it didn't track a SELECT op). – John Dunagan Sep 19 '13 at 15:43
3

This should get you close to what you want.

SELECT name, crdate 
FROM master..sysdatabases
sasonic
  • 764
  • 6
  • 13
  • As of SQL Server 2005, it's recommended to use the catalog views in the `sys` schema (rather than the deprecated `sysdatabases` and `sysobjects` etc. views) – marc_s Sep 18 '13 at 15:27
  • *This SQL Server 2000 system table is included as a view for backward compatibility. We recommend that you use the current SQL Server system views instead. To find the equivalent system view or views, see Mapping SQL Server 2000 System Tables to SQL Server 2005 System Views. This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.* (http://technet.microsoft.com/en-us/library/ms179900.aspx) – marc_s Sep 18 '13 at 15:29
3
create table #db_name (db_name nvarchar(128), last_change datetime);
exec sp_MSForEachDB 'Use ?; insert into #db_name (db_name, last_change) select ''?'', max(modify_date) from sys.tables'
select * from #db_name order by last_change desc

this is not exactly one select but at least you got what you want. I'm db_owner on one of our databases and probably nothing impressive server-wide so it's not very demanding.

nimdil
  • 1,361
  • 10
  • 20
  • Though I can't confirm it will work on 2005 as I have only 2008+ available, so that may be another obstacle :/ – nimdil Sep 18 '13 at 15:57