Agent Jobs

How Old Is Your Database

Here’s some quick SQL Logic to show you how long a database has been around. Sure; you can check the create_date, but this will show you a report on how long it’s been.

select 
	'database'			= sd.name
,	'created on'		= sd.create_date
,	'human readable'	= 
							+ cast(datediff(second, sd.create_date, getdate()) / 60 / 60 / 24 / 30 / 12 as nvarchar(50)) + ' years, '
							+ cast(datediff(second, sd.create_date, getdate()) / 60 / 60 / 24 / 30 % 12 as nvarchar(50)) + ' months, '
							+ cast(datediff(second, sd.create_date, getdate()) / 60 / 60 / 24 % 30 as nvarchar(50)) + ' days, '
							+ cast(datediff(second, sd.create_date, getdate()) / 60 / 60 % 24 as nvarchar(50)) + ' hours, '
							+ cast(datediff(second, sd.create_date, getdate()) / 60 % 60 as nvarchar(50)) + ' minutes '
							+ cast(datediff(second, sd.create_date, getdate()) % 60 as nvarchar(50)) + ' seconds '
from
	sys.databases sd
where
	sd.database_id > 4
order by
	sd.create_date desc
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s