Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Show last modified date in Management Studio

Posted on 2007-08-06
12
Medium Priority
?
327 Views
Last Modified: 2012-06-21
I am using SQL Server 2005.  In Management Studio, I can see the date a Table, SPROC or View was created, but is there any way to show the last modified date?
0
Comment
Question by:wsturdev
  • 4
  • 4
  • 4
12 Comments
 
LVL 43

Expert Comment

by:Eugene Z
ID: 19637956
No,
there is not such field
0
 
LVL 28

Expert Comment

by:imran_fast
ID: 19637981
you'll be able to see this information along with auditing of all of the other objects in SQL Server 2005.
click on your database on sql managemnt studio and then click f7
then you will able to see the summary page on that page then in reports click on schema changes history.
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 19638033
you can use 3rd party tools such as idera sqlcomplince manager
or DDL Triggers , etc
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 28

Accepted Solution

by:
imran_fast earned 2000 total points
ID: 19638055
@EugeneZ
In Sql 2005 management studio there are reports which are part of sql managment studio and those reports give all these required information.
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 19638251
imran_fast -good one
It is true and the report is working from SSIS for sql server 2005
but sql server 2000
---------------
0
 
LVL 28

Expert Comment

by:imran_fast
ID: 19638272
@eugenez
but for sql server 2000 nothing is there.
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 19638461
it is true
0
 
LVL 1

Author Comment

by:wsturdev
ID: 19707560
imran_fast --

Sorry for the delay.  I jsut got back to this issue, and when I followed your instructions, I see the following list:
Database Diagrams
Tables
Views
Synonyms
Programmability
Service Broker
Storage
Security

but no Reports.
0
 
LVL 1

Author Comment

by:wsturdev
ID: 19707645
I found the reports capability and the Schema Change history, but how do I change the date so I can go back a few weeks, and not just a couple of days?
0
 
LVL 28

Expert Comment

by:imran_fast
ID: 19725192
it will give all the changes related to particular object since its creation.
0
 
LVL 1

Author Comment

by:wsturdev
ID: 19725906
And a Restore is considered a Creation?  I restored an older backup and when I went in the only schema change on record was the drop of the database I was restoring, and the date time was that of the restoration.

I there a setting I can use so I can see all activity in a restored database as if I had never backed it up and restored it?
0
 
LVL 1

Author Comment

by:wsturdev
ID: 20125150
Unfortunately, I have come to find out that the reports are tied to the currently active trace, and since the trace on my development copy of SQL Server gets reset frequently, it does not provide the longer term information I want.

However, I found out this will give me what I want:

SELECT name, modify_date FROM sys.views  [or sys.tables, etc.]
order by modify_date desc
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question