How to find the last updated tables in a database

Hi,

I am trying to create a data dictionary for a huge application which has aroung 300 tables in the database....when i perform any operation in the application some tables are updated.... can you help me to find out how can we find out the last updated tables in the database ?

Try using SQL Profiler to monitor which tables are being updated.

You can also search for SQL Dependency Analyzer - there are a few tools out there that will track this.

cheers,

Andrew

How to find out which field in a table is updated

When someone modifies a field in a table, I like to find out which
field he/she changed.
Currently, only the LAST person to modify an object is saved. For
example, if I modify an Application record, I will see "MyName" in the
"tblApplications.UpdatedBy" field and the date and time I updated it.
But it doesn't keep an historical record. We would like these changes
to be stored in a file or a table or something.
Please helpThen implement triggers to log to an audit table the changes to a data table

--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

"lphuong" <lphuong@.neh.gov> wrote in message
news:b6a732a8.0404300532.30de1e3c@.posting.google.c om...
> When someone modifies a field in a table, I like to find out which
> field he/she changed.
> Currently, only the LAST person to modify an object is saved. For
> example, if I modify an Application record, I will see "MyName" in the
> "tblApplications.UpdatedBy" field and the date and time I updated it.
> But it doesn't keep an historical record. We would like these changes
> to be stored in a file or a table or something.
> Please help|||Allan, would you please show me how to do it in SQL Enterprise or in
VB6. I'm a novice in this subject.
Thank you.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||OK

--So say you have a table

CREATE TABLE MyRealTable(ColPK int identity(1,1) Primary Key, col1 int)

--You now emulate that table with an audit version

CREATE TABLE Audit_MyRealTable(ColPK int, col1 int)

--You now need an auditing trigger for INSERT, UPDATE, DELETE. I prefer 1
trigger per action.

--Here is the update trigger

CREATE TRIGGER tr_u_MyRealTable ON MyRealTable FOR UPDATE
AS
INSERT Audit_MyRealTable(ColPK, col1)
SELECT ColPK, col1 FROM UPDATED
GO

--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

"L Phuong" <lphuong@.neh.gov> wrote in message
news:4092936d$0$202$75868355@.news.frii.net...
> Allan, would you please show me how to do it in SQL Enterprise or in
> VB6. I'm a novice in this subject.
> Thank you.
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

How to find out when the database has been accessed/updated last?

Hi all, is there a way to find out when the database has been accessed last
or when any of the tables has been updated recently?Look into C2 auditing
--
Thomas
"Ruski" wrote:
> Hi all, is there a way to find out when the database has been accessed last
> or when any of the tables has been updated recently?|||Ruski,
You would need to have had Profiler or some third-party auditing tool
running to determine this. Another option would be to use some extra
auditing code in the tables or use triggers. C2 is probably overkill for
this.
HTH
Jerry
"Ruski" <Ruski@.discussions.microsoft.com> wrote in message
news:206D75AD-7369-436F-8650-F52D8FA863BB@.microsoft.com...
> Hi all, is there a way to find out when the database has been accessed
> last
> or when any of the tables has been updated recently?|||Thanks, guys for you help. I am doing an audit of the sql farm and some of
the database has been left there for years and nobody knows what they are.
Stilt we just can't delete them. I do run profiler on the servers I am
working on to find out who logs in/out and log the data for further analysis.
But what I wanted is just from looking at the random database determine when
it is accessed/updated last and by whom. Some of the datatabase have the
AuditTrail tables and DateDelete/Added in the columns and I can determine
this information but other databases don't.
Anyway, thanks a log 4 your prompt response.
"Jerry Spivey" wrote:
> Ruski,
> You would need to have had Profiler or some third-party auditing tool
> running to determine this. Another option would be to use some extra
> auditing code in the tables or use triggers. C2 is probably overkill for
> this.
> HTH
> Jerry
> "Ruski" <Ruski@.discussions.microsoft.com> wrote in message
> news:206D75AD-7369-436F-8650-F52D8FA863BB@.microsoft.com...
> > Hi all, is there a way to find out when the database has been accessed
> > last
> > or when any of the tables has been updated recently?
>
>

How to find out when the database has been accessed/updated last?

Hi all, is there a way to find out when the database has been accessed last
or when any of the tables has been updated recently?
Look into C2 auditing
Thomas
"Ruski" wrote:

> Hi all, is there a way to find out when the database has been accessed last
> or when any of the tables has been updated recently?
|||Ruski,
You would need to have had Profiler or some third-party auditing tool
running to determine this. Another option would be to use some extra
auditing code in the tables or use triggers. C2 is probably overkill for
this.
HTH
Jerry
"Ruski" <Ruski@.discussions.microsoft.com> wrote in message
news:206D75AD-7369-436F-8650-F52D8FA863BB@.microsoft.com...
> Hi all, is there a way to find out when the database has been accessed
> last
> or when any of the tables has been updated recently?

How to find out when the database has been accessed/updated last?

Hi all, is there a way to find out when the database has been accessed last
or when any of the tables has been updated recently?Look into C2 auditing
--
Thomas
"Ruski" wrote:

> Hi all, is there a way to find out when the database has been accessed las
t
> or when any of the tables has been updated recently?|||Ruski,
You would need to have had Profiler or some third-party auditing tool
running to determine this. Another option would be to use some extra
auditing code in the tables or use triggers. C2 is probably overkill for
this.
HTH
Jerry
"Ruski" <Ruski@.discussions.microsoft.com> wrote in message
news:206D75AD-7369-436F-8650-F52D8FA863BB@.microsoft.com...
> Hi all, is there a way to find out when the database has been accessed
> last
> or when any of the tables has been updated recently?

How to find out when a database was last updated

I want to find out when a database was last updated.
I checked in IE, it shows the creation time and the time last backup was
taken.
I looked at the timestamp of the database, that doesn't seem to reflect the
correct date and time, in my case it is showing the date of 12/4/2005 11:00am
whereas I know for sure that the database is being updated since after that.
That was the time I took last backup of this database.
Thank you in advance,
-MeUnless you have a trace running you can't. Trace is your best bet to see
if and when a db is being accessed. You can also potentially use some of
the third party Log viewer tools.
--
Andrew J. Kelly SQL MVP
"Me" <Me@.discussions.microsoft.com> wrote in message
news:F89147AA-3F98-4ECC-8981-27854F64F5C6@.microsoft.com...
>I want to find out when a database was last updated.
> I checked in IE, it shows the creation time and the time last backup was
> taken.
> I looked at the timestamp of the database, that doesn't seem to reflect
> the
> correct date and time, in my case it is showing the date of 12/4/2005
> 11:00am
> whereas I know for sure that the database is being updated since after
> that.
> That was the time I took last backup of this database.
> Thank you in advance,
> -Me
>

How to find out when a database was last updated

I want to find out when a database was last updated.
I checked in IE, it shows the creation time and the time last backup was
taken.
I looked at the timestamp of the database, that doesn't seem to reflect the
correct date and time, in my case it is showing the date of 12/4/2005 11:00am
whereas I know for sure that the database is being updated since after that.
That was the time I took last backup of this database.
Thank you in advance,
-Me
Unless you have a trace running you can't. Trace is your best bet to see
if and when a db is being accessed. You can also potentially use some of
the third party Log viewer tools.
Andrew J. Kelly SQL MVP
"Me" <Me@.discussions.microsoft.com> wrote in message
news:F89147AA-3F98-4ECC-8981-27854F64F5C6@.microsoft.com...
>I want to find out when a database was last updated.
> I checked in IE, it shows the creation time and the time last backup was
> taken.
> I looked at the timestamp of the database, that doesn't seem to reflect
> the
> correct date and time, in my case it is showing the date of 12/4/2005
> 11:00am
> whereas I know for sure that the database is being updated since after
> that.
> That was the time I took last backup of this database.
> Thank you in advance,
> -Me
>

How to find out when a database was last updated

I want to find out when a database was last updated.
I checked in IE, it shows the creation time and the time last backup was
taken.
I looked at the timestamp of the database, that doesn't seem to reflect the
correct date and time, in my case it is showing the date of 12/4/2005 11:00a
m
whereas I know for sure that the database is being updated since after that.
That was the time I took last backup of this database.
Thank you in advance,
-MeUnless you have a trace running you can't. Trace is your best bet to see
if and when a db is being accessed. You can also potentially use some of
the third party Log viewer tools.
Andrew J. Kelly SQL MVP
"Me" <Me@.discussions.microsoft.com> wrote in message
news:F89147AA-3F98-4ECC-8981-27854F64F5C6@.microsoft.com...
>I want to find out when a database was last updated.
> I checked in IE, it shows the creation time and the time last backup was
> taken.
> I looked at the timestamp of the database, that doesn't seem to reflect
> the
> correct date and time, in my case it is showing the date of 12/4/2005
> 11:00am
> whereas I know for sure that the database is being updated since after
> that.
> That was the time I took last backup of this database.
> Thank you in advance,
> -Me
>

how to find out if a TSQL table is being updated?

how to find out if a TSQL table is being updated?
for example if someone does an update to a table that takes a long time, is
there anyway for me to check the status of that update process?
There is no built-in and generally applicable method to determine the
progress of an UPDATE (e.g. to be able to answer how much longer it will take
for the UPDATE to complete). The reason being that UPDATE can be processed in
many different way.
If we are talking about a very large update, you may want to break it into
multiple smaller batches of update, and insert code yourself to report the
progress.
Linchi
"DR" wrote:

> how to find out if a TSQL table is being updated?
> for example if someone does an update to a table that takes a long time, is
> there anyway for me to check the status of that update process?
>
>

how to find out if a TSQL table is being updated?

how to find out if a TSQL table is being updated?
for example if someone does an update to a table that takes a long time, is
there anyway for me to check the status of that update process?Hi
What do you mean by a long time? If there are a significant number of rows
being updated then you may want to "batch" the update so that only a specific
number are updated and use a loop until all is complete. This may reduce the
number or extent of the locks on the table and reduce contention. sp_lock
will show the locks.
You could then output the number of iterations, but unless you know the
total number of rows to be updated this may not be useful.
John
"DR" wrote:
> how to find out if a TSQL table is being updated?
> for example if someone does an update to a table that takes a long time, is
> there anyway for me to check the status of that update process?
>
>

how to find out if a TSQL table is being updated?

how to find out if a TSQL table is being updated?
for example if someone does an update to a table that takes a long time, is
there anyway for me to check the status of that update process?
Answered in .programming. Please do not double-post. If you want to post
to multiple newsgroups, post to all relevant groups at once so people will
not waste time trying to answer questions that have already been answered.
"DR" <softwareengineer98037@.yahoo.com> wrote in message
news:uaiJVZ7MIHA.5224@.TK2MSFTNGP02.phx.gbl...
> how to find out if a TSQL table is being updated?
> for example if someone does an update to a table that takes a long time,
> is there anyway for me to check the status of that update process?
>

how to find out if a TSQL table is being updated?

how to find out if a TSQL table is being updated?
for example if someone does an update to a table that takes a long time, is
there anyway for me to check the status of that update process?
Hi
What do you mean by a long time? If there are a significant number of rows
being updated then you may want to "batch" the update so that only a specific
number are updated and use a loop until all is complete. This may reduce the
number or extent of the locks on the table and reduce contention. sp_lock
will show the locks.
You could then output the number of iterations, but unless you know the
total number of rows to be updated this may not be useful.
John
"DR" wrote:

> how to find out if a TSQL table is being updated?
> for example if someone does an update to a table that takes a long time, is
> there anyway for me to check the status of that update process?
>
>

how to find out if a TSQL table is being updated?

how to find out if a TSQL table is being updated?
for example if someone does an update to a table that takes a long time, is
there anyway for me to check the status of that update process?Hi
What do you mean by a long time? If there are a significant number of rows
being updated then you may want to "batch" the update so that only a specifi
c
number are updated and use a loop until all is complete. This may reduce the
number or extent of the locks on the table and reduce contention. sp_lock
will show the locks.
You could then output the number of iterations, but unless you know the
total number of rows to be updated this may not be useful.
John
"DR" wrote:

> how to find out if a TSQL table is being updated?
> for example if someone does an update to a table that takes a long time, i
s
> there anyway for me to check the status of that update process?
>
>