Which table is used last

Question

Which table is used last

parthi-1705

SSCrazy Eights

Points: 9277
More actions
October 27, 2010 at 5:46 am

#228352

Hi
In a Database i am having nearly 200 plus tables i need to find the tables which are used last.i.e say Table5,Table8,Table55,Table87,Table19,Table125,Table159,Table43,Table186,Table68 this are last 10 tables which has insertion or updation or deletion.Means that datamodification has been taken place in this tables i need each and every table when the data modification has been taken place.
Like: Last 10 table, used time
Table68 @ 27/10/2010 12:10:00:256 PM
Table86 @ 27/10/2010 12:09:52:506 PM
.......
Table5 @ 27/10/2010 12:03:05:549 PM
Thanks
Parthi
Thanks
Parthi

Viewing 15 posts - 1 through 15 (of 21 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Server side trace, maybe extended events, triggers will also work since you mean modification only. The info is not tracked automatically.

I seriously hope those are not the real names of your tables

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

parthi-1705 SSCrazy Eights Points: 9277 More actions · Answer 2

GilaMonster (10/27/2010)
Server side trace, maybe extended events, triggers will also work since you mean modification only. The info is not tracked automatically.
I seriously hope those are not the real names of your tables

Hi

Apart from trigger

I dont want to run trace since i may need to check at any time

Yes it not real table names,i kept same as such

Thanks

Parthi

Thanks
Parthi

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Server side trace, triggers. Maybe extended events, I'm not familiar with them. Also maybe worth a look into Change tracking/change data capture and SQL audit.

There's a fair few more options on SQL 2008 than on earlier versions

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

parthi-1705 SSCrazy Eights Points: 9277 More actions · Answer 4

GilaMonster (10/27/2010)
Server side trace, triggers. Maybe extended events, I'm not familiar with them. Also maybe worth a look into Change tracking/change data capture and SQL audit.
There's a fair few more options on SQL 2008 than on earlier versions

Hi

In CDC it will behave as replication and thats too it will keep another table to track the details, but this is not my case. i need to track the info in a query or tro some thing else

Thanks

Parthi

Thanks
Parthi

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

There's no simple query that will return this information. I've told you the possible ways, now you need to figure out which you can use, adapt and implement to get your information.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

parthi-1705 SSCrazy Eights Points: 9277 More actions · Answer 6

Any other 3rd party tool can solve this.

Trigger or not good to used on all the tables in the DB

Server side trace may be good for one point of time but in random selection how it can be

can we solve this by any other means

Thanks

Parthi

Thanks
Parthi

Lowell SSC Guru Points: 323507 More actions · Answer 7

parthi-1705 (10/27/2010)
Any other 3rd party tool can solve this.
Trigger or not good to used on all the tables in the DB
Server side trace may be good for one point of time but in random selection how it can be
can we solve this by any other means
Thanks
Parthi

Third party tools like log readers can show you the last changes...but not produce the report you are looking for. you'd have to save the results from the log reader to a file or table, then try to get your "last 10" or whatever. aside from the rather large cost for the software, it'd still require gathering the results into a table/format so they can be aggregated, then write the aggregation SQL and run it.

DML triggers are the best solution, as Gail said, becaus ethey are limited to the changes you wanted...insert/update/delete. if i needed this info, this is what i would use.

a server side trace could provide what you are looking for, but since you are not familiar with it, you skip past that solution as well.

there is no query that can give you the instant report you are looking for, and any solution you add will only capture changes going forward.

this information(last 10 items changed since i last checked) is more of a "nice to know" bit of information, and certainly not anything that is mission critical. in my database, the application might save a suite of 40 or 50 tables of related information based on a single "click" of the save button; so a query like you are after would not be very helpful at all.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Steve Jones - SSC Editor SSC Guru Points: 734691 More actions · Answer 8

You can save/load trace information into a table.

Gail and Tom have given you the options that will work best. There is no other object that tracks this stuff.

winash SSChampion Points: 11446 More actions · Answer 9

As Gail already mentioned - did you look at SQL Audit? You can audit actions (insert/update/delete/select) for specified tables (by specified logins) and have this information logged into a file which you can then access using the fn_get_audit_file function (like a SELECT * FROM fn_get_audit_file(....) WHERE ....)

Brandie Tarvin SSC Guru Points: 173009 More actions · Answer 10

parthi-1705 (10/27/2010)
Any other 3rd party tool can solve this.

Really? I haven't seen one that doesn't use traces or triggers. Please, tell us what those tools are. I'd like to test them out.

We're currently using triggers to load the modified data into a table as an XML datatype.

EDIT: It occurs to me that you may have meant that as a question, not a statement. If so, I don't have an answer.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

parthi-1705 SSCrazy Eights Points: 9277 More actions · Answer 11

Brandie Tarvin (10/27/2010)
Really? I haven't seen one that doesn't use traces or triggers. Please, tell us what those tools are. I'd like to test them out.

You don't have the tool??? :-D:-D:-D...You need to Test what ??? ;-);-);-).

I dont have idea thats why i have posted here.

You too dont know how to get the output thats why you are asking me too provide the tool. :-P:-P:-P It is so crazy. :-P:-P:-P

Thanks

Parthi

Thanks
Parthi

Brandie Tarvin SSC Guru Points: 173009 More actions · Answer 12

Read my edit. Your punctuation use (. instead of ?) at the end made it sound like a statement, not a question. I thought you were telling us there were third party tools. Then I realized you might have been asking and gotten the punctuation wrong.

So I edited my post.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

GSquared SSC Guru Points: 260824 More actions · Answer 13

I think you have misinformation about how a server-side trace works. You can use one to track queries, and keep the files from it for however long you want, and you can query it very easily. It will do exactly what you need.

If you don't want to learn how to do that, then you'll need to implement a log-parsing solution. ApexSQL and Lumigent (I think) provide tools that can be used to query the transaction logs. Those can be used to find out what data has been modified in which tables. However, they are nowhere near so easy to use as a server-side trace, they don't track select statements, just insert/update/delete, and they cost money. They are also slower to query.

If you aren't sure how to use a trace to get what you need, ask questions here.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Johan Bijnens SSC Guru Points: 135274 More actions · Answer 14

I see no tuples in your table list, does that mean you only need to know the last time a table has changed ?

Do you have to datetime_updated column in your tables ?

( If yes, I hope you keep them up to date using a trigger ! )

If yes, will selecting the full of both and excluding the unchanged be an option ? ( except clause )

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me