How do you tell which is the most updated SQL database? The server that has the most recent entries.

Question

How do you tell which is the most updated SQL database? The server that has the most recent entries.

Joshua Tay 246

Hi,

I was given a server to manage my ERP, I have 5 databases siting on a server.
How do you tell which is the most updated SQL database?

Thank you.

Answer accepted by question author

0 additional answers

Your answer

Answer 1

Nandan Hegde 36,716 MVP Volunteer Moderator

Hey,
What do you mean by most updated one?

There can be multiple scenarios for the same:

1) Right click on the database>> Properties and you can see Date Created.
This shows you when a database was created.
So in some scenarios the latest date meaning the most updated database or it can also mean a old database backup that was restored recently.

2) You can check the information_schema details and log details of database to check where the objects are

Joshua Tay 246 Reputation points

2021-03-16T06:18:23.3+00:00

Hi NandanHegde-7720,

I forgot to mentioned that by The most updated one", I meant to say the database that consist of the latest entries, or modified most recently.

May I know where does the Information_schema resides at?

Thank you!

Regards,
Josh
Nandan Hegde 36,716 Reputation points MVP Volunteer Moderator

2021-03-16T08:59:34.627+00:00

Hey you can execute the below queries:

Select * from information_schema.tables :
This would give you the list of objects (tables and views) in database.

you can also execute the below query:

Table last queried:
select
t.[name],
t.create_date,
t.modify_date,
DateDiff(day, t.create_date, t.modify_date) as Days_Between_Create_and_Modified,
DateDiff(day, t.modify_date, GetDate()) as Days_Since_LastModified,
DateDiff(month, t.modify_date, GetDate()) as Months_Since_LastModified,
DateDiff(day, i.LastUsed, GetDate()) as Days_since_Table_Queried
from sys.schemas s join sys.tables t on t.schema_id = s.schema_id
outer apply (
select
(select Max(LastUsed)
from (values (last_user_update), (last_user_seek), (last_user_scan), (last_user_lookup)) as value(LastUsed))LastUsed
from sys.dm_db_index_usage_stats i
where i.object_id=t.object_id
)i

Share via

How do you tell which is the most updated SQL database? The server that has the most recent entries.

0 additional answers

Your answer