question

Joshlabtech-6194 avatar image
0 Votes"
Joshlabtech-6194 asked NandanHegde-7720 commented

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

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?

78083-database.jpg

Thank you.


sql-server-general
database.jpg (12.4 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

NandanHegde-7720 avatar image
0 Votes"
NandanHegde-7720 answered NandanHegde-7720 commented

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

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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

0 Votes 0 ·

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



1 Vote 1 ·